Shareware Super Platinum 8

home *** CD-ROM | disk | FTP | other *** search

/ Shareware Super Platinum 8 / Shareware Super Platinum 8.iso / mac / DATABASE / REBEL30.ZIP;1 / CHAP5-11.DOC < prev next >

Wrap

Text File | 1993-03-10 | 176.3 KB | 3,753 lines

_______________________ ______________________________________________/ Chapter 5 EDIT MODE From the Input Line, your editing capabilities are limited to erasing the last character typed with the <Back Space> key. In addition, there is no way of modifying the contents of existing cells - short of retyping them. Obviously, if you are working with long complicated formulas, this could be a tedious way of correcting errors or making changes. To provide you with more flexibility in this area, a special set of function key options are available, that will allow you to revise the contents of existing cells. Specifically, you will be able to; 1) use the arrow keys to move the input cursor; 2) delete characters; 3) insert new characters; 4) search and replace patterns of characters; 5) 'capture' a series of characters to be re-inserted at a different location within the same or different entries; 6) incorporate the formulas and labels of other cells into the cell being edited; 7) locate closing brackets; 8) display error messages; and 9) single step through formula calculations, allowing you to check the intermediate results of each operation as an expression is evaluated. To enter the 'Edit Mode', move the Cell Pointer to the cell you wish to modify and press the <F2> function key TWICE from the Root Menu. The contents of that cell along with the Edit Mode Function Key Options should appear just above the Column Bar. You can now use the arrow keys to move the input cursor to any location within the entry. The <Up> arrow will move you immediately to the beginning of the entry, while the <Down> arrow will move you back to the end. There is also an 'insert' and 'overlay' character mode that will allow you to insert new characters or overwrite existing ones. The F1 (INS) option will toggle you between them (refer to the section below). And, you can use either the <Del> key or F2 (DEL) option to delete characters. Try entering the Edit Mode with the Cell Pointer positioned on an empty cell and experimenting with each of these options. Remember, you can use the <Esc> key at any time to abort. After you have made your changes, you can save them by pressing the <Enter> key. If a problem is detected with an expression when exiting the Edit Mode, an error message will appear at the top of your screen and you will be returned to make the necessary changes. This differs from the way in which errors are handled from the Input Line. Here, no error messages will appear. Instead, only a series of question marks (????) will be displayed in the cell(s) that have detected a problem and you are allowed to continue as though nothing has happened. You can get a general idea of what went wrong in these cases by moving the Cell Pointer to the cell that contains the question marks and then entering the Edit Mode. This will cause an error message to be displayed that will hopefully help you determine what when wrong. If you are unable to correct the problem from within the Edit Mode, use the <Esc> key to exit. You can return at a later time to fix the problem. Be aware, however, that when you abort the Edit Mode (after an error has been detected), the spreadsheet will sometimes insert a single quote (') at the beginning of the expression. If this happens, you must remove the quote before the expression can be re-evaluated. ......... : f2 : From the "Root Menu", press F2 : EDIT : ......... : f2 : then press F2 again : Edit : _____ _____ _____ _____ _____ EDITING: |1 INS| |2 DEL| |3 RPL| |4 GO| |5 CUT| . . . f1 - Toggles between INSERT and OVERWRITE entry modes. When the descriptive word is displayed in capital letters (INS), you're in the 'overwrite' mode. This means that any character you type will be added to the end of the entry or will overwrite an existing character, depending on the cursor position. When the insert identifier is displayed with lower case letters (ins), you're in the 'insert' mode. In this case, new characters are placed between existing characters. f2 - This key DELETES the character at the current cursor position. It works just like the <Del> key on your keyboard. f3 - SEARCHES the entry for a pattern of characters and REPLACES them with another pattern. You will be prompted to enter both of these patterns. f4 - MOVES the Cell Pointer to another cell location on the worksheet - without forcing you to exit the Edit Mode. This allows you to view the contents of other cells; and, if you like, to use the F7 (LBL) option to incorporate them into the current entry. f5 - CUTS (captures) a series of characters from the text that is currently being edited, placing them into the 'cut' buffer. This buffer can then be re-inserted at any location within the entry. To execute the command, press the F5 key with the cursor positioned on the first character of the string you wish to capture. Additional characters will continue to be placed into this buffer while the cursor is moved to the right with the Right <Arrow> Key. Do not press any other key (other than the right arrow key) until AFTER you have TERMINATED THE 'CUT' OPERATION BY PRESSING THE F5 KEY A SECOND TIME. The buffer's contents are preserved until the process is repeated (i.e. they are not lost when you exit the Edit Mode). This allows you to insert the buffer into other cells, as well. The F6 (PUT) option is used to copy the contents of the 'cut' buffer back into the entry, at any cursor position you like. _____ _____ _____ _____ _____ . . . |6 PUT| |7 LBL| |8 ADR| |9 DBG| | [()]| f6 - PUTS (inserts) the contents of the 'CUT' buffer into the entry at the current cursor location (see the F5 'CUT' option). f7 - Inserts the 'formula' or 'label' of the cell that is highlighted by the Cell Pointer into the entry - at the current cursor location. The F4 (GO) command can be used to move the Cell Pointer to the cell that contains statement you wish to capture. While in the Edit Mode, the <Home>, <End>, <Pg Up>, and <Pg Dn> Keys can also be used to move the Cell Pointer, one cell at a time. f8 - This option works like the F7 (LBL) command except that it inserts the 'Cell Address' of the cell that is highlighted by the Cell Pointer into the current entry. f9 - This command is used to debug a 'formula' by allowing you to evaluate it - one step at a time. After each operation, it will pause and display the value calculated or substituted. Press the <Enter> key to continue to the next operation. f0 - With the cursor placed on any square bracket [] or parenthesis (), this option will cause the cursor to jump to its CLOSING bracket. This command also works with the ?:; (arithmetic-if) delimiters. ____________________________ _________________________________________/ Chapter 6 TECHNICAL INFO This chapter is designed to provide you with a more detailed explanation of the more important topics covered thus far. Every attempt has been made to keep each of these sections as short and to the point as possible. The first six contain most of the what you'll need to use REBEL effectively. It's highly recommended, however, that you read them all - in the order they are presented, since each section tends to build on the information that precedes it. Chapter Contents Section 6:1 CELL TYPES Section 6:2 CELL ADDRESSES Section 6:3 OPERATORS Section 6:4 FORMULAS Section 6:5 ARRAYS Section 6:6 MULTIPLE WORKSHEETS Section 6:7 RANGE OPERATIONS Section 6:8 DIRECT CELL ADDRESSES Section 6:9 INDIRECT CELL ADDRESSES Section 6:10 WHEN DO FORMULAS GET RECALCULATED? Section 6:11 FORCING CELLS TO RECALCULATE Section 6:12 OPTIMIZING YOUR WORKSHEET Section 6:13 MISCELLANEOUS TOPICS ----------------------------------------------------------------------- Section 6:1 CELL TYPES ----------------------------------------------------------------------- A FORMULA, LABEL, or VARIABLE LABEL cell is created each time an entry is made (limited to 255 characters) from the Input Line. The specific type of cell created depends on whether the entry is interpreted as a mathematical expression (a formula) or a string of characters (a label). For the most part, this is done automatically by the spreadsheet, which will attempt to make an educated guess based on the first character of your entry. It is important that the correct decision is made (and in most cases it is), since the cell 'type' governs the way in which a cell's data can be used by other cells. FORMULA CELLS are created whenever a mathematical expression (formula) is entered at the Input Line. This can be anything from a simple number to a complex set of arithmetic operations. To be interpreted as such, it must begin with one of the following characters: + - . [ ( 0 1 2 3 4 5 6 7 8 9 @ There are, however, times when an otherwise valid expression may not begin with one of the above characters (i.e. expressions that begin with function calls). In these situations, you must FORCE the spreadsheet to interpret your entry as a 'formula' by beginning it with a plus sign (+). For example, the formula "sqrt(16)" should be entered as "+sqrt(16)" to prevent it from being interpreted as a 'label'. The plus sign (+) will not affect the results. LABEL CELLS are created whenever an entry is made that DOES NOT begin with one of the characters that denotes a formula. Labels consist of a string of characters that appear on the screen exactly as they are typed from the Input Line. The spreadsheet makes no attempt to interpret these entries in any way. If a label is longer than the column width of the cell that it's stored, it will overlap up to to 63 adjacent empty cells before its display is finally terminated. Just as with formulas, there are occasions when a label will begin with a character that can cause some confusion. For example, the string "1st of January" begins with a '1'. If typed in as it stands, the spreadsheet will look at the first character and think: "this is a formula", and it will create a FORMULA CELL to store it in. This, of course, will produce an error when the spreadsheet attempts to calculate its value! That is, what does "1st of January" mean mathematically? It is easy to tell when this has occurred by the question marks (?????) that are displayed by the cell instead of the label you expected. The solution is simple - just begin the label with a single quote ('). This will FORCE the spreadsheet to view the entry as a label, rather than a formula. The leading single quote will not appear when the string is displayed. VARIABLE LABEL CELLS are created ONLY when labels are entered that begins with a double quote ("). Unlike the single quote ('), which is often not necessary for standard labels, THE DOUBLE QUOTE IS REQUIRED FOR VARIABLE LABELS. Like standard labels, variable labels display everything in the character string (just as its appears) - except for Cell Addresses. When a Cell Address (e.g. [1,2]) is encountered, the contents of THAT cell is displayed, rather than the Cell Address itself. For example, if cell [1,2] contains the string "red", an entry such as this: "The car is [1,2]. would be displayed as: The car is red. UNDEFINED CELLS are automatically created by the spreadsheet when a reference is made to an 'empty' cell. These cells are easily identified by the long underscore (________) they display. They can be removed either by eliminating the references to them or by making a valid entry in their place. Normally, a reference to an UNDEFINED (empty) cell within a formula will produce questionable results. When this occurs, a series of question marks (?????) will appear in the display of the cell that contains the reference. You can locate the UNDEFINED cells that another cell is addressing with the (~) Cell Trace Command that is described in Chapter 7. ----------------------------------------------------------------------- Section 6:2 CELL ADDRESSES ----------------------------------------------------------------------- A CELL ADDRESS (e.g. [row,column]) is simply a way of referencing another cell's value within a formula. Not only do Cell Addresses allow you to access the values of other cells, but they also cause the formulas that use them to be recalculated when the values of the cells they reference are changed. Cell Addresses are composed of four (4) parts or fields (the Row, Column, Array Element, and Worksheet Level) separated by commas and enclosed within square brackets []. Of these, only the 'Row' and 'Column' fields that define the cell's position on the worksheet are required. REQUIRED________ _______________ OPTIONAL | | | | [Row,Column,(Array),(Level)] | | The ARRAY ELEMENT field __| |____ The WORKSHEET LEVEL field is is only needed when only needed when accessing accessing array values cells at a different level The ARRAY ELEMENT field is optional and, if not used, will default to element position (0). This position is reserved by a FORMULA cell to store the value that is the result of evaluating its formula. It is the only position assigned to a FORMULA cell when it is first created and is generally not considered part of the array space. As a result, array element numbers greater than (0) take on no real meaning until an array area (that can range from 1 to 8000 elements) is assigned to the cell. Once this is done, however, each of these element positions can be assigned a value that in turn can be accessed through this field. Although an array space can not be assigned to a LABEL cell, this field can be used to address the individual characters of the label itself. The length of the label governs the largest element number that can be accessed. For additional information, see Section 6:5 on Arrays. The WORKSHEET LEVEL field is also optional and defaults to the zero (0) when left blank. A zero (0) in this field will always be replaced by the spreadsheet with the level number (ranging between 1 and 4) of the cell that contains the reference. This is what allows a Cell Address to be copied to virtually any level without requiring it to be modified. When a value greater than zero (0) is loaded into this field, it will result is an absolute reference to a cell at that level. Keep in mind, the Cell Addresses that uses this field may need to be modified if the worksheet that contains them is later loaded at a different level. It's always best to leave this field BLANK when accessing cells at the same worksheet level (refer to Section 6:6 - Multiple Worksheets). Examples: [1,2] ==> Makes a reference to the 'value' in the cell located at row 1, column 2 of the worksheet level that contains the formula. [1,2,0] ==> same as above. [1,2,,4] ==> Makes an cell reference to the value in the cell located at row 1, column 2 of worksheet level 4. [1,2,3] ==> References the value in the 3rd array element of the cell located at row 1, column 2 of the worksheet level that contains the formula. NOTE: The Row, Column, Array, and Worksheet fields can themselves be Cell Addresses. ----------------------------------------------------------------------- Section 6:3 OPERATORS ----------------------------------------------------------------------- The operators provided by this spreadsheet are patterned after those defined in the 'C' Programming Language - with a few minor exceptions. The precedence of each operator is listed in following table from highest to lowest priority. Parenthesis (), of course, can be used at any time to alter the order in which operations are performed. UNARY OPERATORS --------------------------------------------------------------------- Op Name Use --> Result Description --------------------------------------------------------------------- ! Logical NOT !0 --> 1 converts 0 (F) to 1 (True) !5 --> 0 converts 5 (T) to 0 (False) - Unary Minus -(-5) --> 5 negates a negative 5 + Unary Plus +(-5) --> -5 has no affect ARITHMETIC OPERATORS --------------------------------------------------------------------- Op Name Use --> Result Description --------------------------------------------------------------------- ** Exponentiation 4**2 --> 16 4 raised to the power of 2 * Multiplication 4*2 --> 8 4 multiplied by 2 / Division 4/2 --> 2 4 divided by 2 % Modulo 5%3 --> 2 remainder of 5 divided by 3 + Addition 5+3 --> 8 5 added to 3 - Subtraction 5-1 --> 4 5 minus 1 NOTE: When using the modulo (%) operator, the values of both expressions are temporarily converted to whole numbers before the operation is performed. The result is always a whole number. BITWISE (SHIFT) OPERATORS --------------------------------------------------------------------- Op Name Use --> Result Description --------------------------------------------------------------------- << Left Shift 2<<3 --> 16 shift the bits that represent the number 2 3 positions to the left >> Right Shift 16>>3 --> 2 shift the bits that represent the number 2 3 positions to the right NOTE: The values of both expressions are temporarily converted to whole numbers before these operations are performed. The result is always a whole number. RELATIONAL OPERATORS --------------------------------------------------------------------- Op Name Use --> Result Description --------------------------------------------------------------------- == Equal to 4==5 --> 0 (F) 4 equals 5 (FALSE or 0) != Not Equal to 2!=3 --> 1 (T) 2 does not equal 3 (TRUE) <= Less or Equal 2<=2 --> 1 (T) 2 is less than or equal to 2 (TRUE or 1) >= Greater or Equal 2>=4 --> 0 (F) 2 is not greater than or equal to 4 (FALSE or 0) < Less than 2<3 --> 1 (T) 2 is less than 3 (TRUE) > Greater than 3>2 --> 1 (T) 3 is greater than 2 (TRUE) BITWISE OPERATORS --------------------------------------------------------------------- Op Name Use --> Result Description --------------------------------------------------------------------- & Bitwise AND 1&3 --> 1 bits are set where corresponding bits of both numbers are the same | Bitwise OR 1|3 --> 3 bits are set when either number has a corresponding bit set NOTE: The values of both expressions are temporarily converted to whole numbers before these operations are performed. The result is always a whole number. LOGICAL OPERATORS --------------------------------------------------------------------- Op Name Use --> Result Description --------------------------------------------------------------------- && Logical AND (1<2)&&0 --> 0(F) if (1<2) is TRUE AND 0 is TRUE (0 is FALSE) then the result is TRUE (1) || Logical OR (1<2)||0 --> 1(T) if (1<2) is TRUE OR 0 is TRUE then the result is TRUE (1) ASSIGNMENT OPERATORS --------------------------------------------------------------------- Op Name Use Description --------------------------------------------------------------------- IMPORTANT! Assignments can only be made to ARRAY ELEMENTS or the SPECIAL REGISTERS associated to Range Operations. = Assignment [1,1,6] = 5 assigns the value (5) to the sixth array element of cell [1,1] += addition x += 5 same as x = x + 5 -= subtraction x -= 5 same as x = x - 5 *= multiplication x *= 5 same as x = x * 5 /= division x /= 5 same as x = x / 5 %= modulo x %= 5 (see note) same as x = x % 5 <<= shift left x <<=5 (see note) same as x = x << 5 >>= shift right x >>=5 (see note) same as x = x >> 5 &= AND (bitwise) x &= 5 (see note) same as x = x & 5 |= OR (bitwise) x |= 5 (see note) same as x = x | 5 NOTE: The Compound Assignments (% << >> & and |) will temporarily convert both operands to whole numbers before computing the results. The result is always returned as a whole number. Example: 1) [1,2,1] = 5 (legal) 2) [1,2] = 5 (illegal) 3) [1,2,1] += 5 (takes the value in [1,2,1], adds 5 to it and places the results back into [1,2,1]) TERNARY CONDITIONAL OPERATOR --------------------------------------------------------------------- Op Name Use --> Result Description --------------------------------------------------------------------- ?:; Arithmetic (2>1)?5:4; --> 5 IF 2 is greater than 1 if-then-else THEN(?) 5 ELSE(:) 4 ____ (optional assignment to an array element) | | (TEST) (IF TRUE) (IF FALSE) [r,c,a] = (Expression#1) ? Expression#2 : Expression#3 ; | | | |_________(REQUIRED)__________| IF: 'Expression#1' results in a non-zero value THEN: The expression that follows the question mark (?) is evaluated (i.e. 'Expression#2') and whatever follows the colon (:) is ignored. ELSE: The expression that follows the colon (:) is evaluated (i.e. 'Expression#3'). Example 1 ARITHMETIC IF-THEN-ELSE IF the contents of cell [1,1] is greater than or equal to the contents of cell [2,2]: THEN multiply the contents of cell [3,3] by 2; ELSE multiple the contents of [3,3] by 4 and assigns the results of array element 6 of cell [1,2]. (optional) (test) (if true) (if false) [1,2,6] = ([1,1]>=[2,2]) ? [3,3]*2 : [3,3]*4; or ([1,1]>=[2,2]) ? [1,2,6]=[3,3]*2 : [1,2,6]=[3,3]*4; ----------------------------------------------------------------------- Section 6:4 'FORMULAS' ----------------------------------------------------------------------- A 'formula' is the expression (or the set of arithmetic operations) that is associated with a FORMULA cell when it is first created. It is recalculated each time a change is made to one of the cells it addresses. Since any cell can have a formula, which in turn can reference the results of other cells, a whole progression of calculations can be performed based on a single change. Any given cell can have only one formula assigned to it. When evaluated, the result is ALWAYS stored at Element position '0' of the Cell Address ([1,2,0] or by default [1,2]), which is reserved for this purpose. Other Element positions (i.e. those associated with the optional ARRAY AREA) can not have 'formulas' assigned to them. This is a subtle, but important, distinction between Element position '0' and those that range between 1 and 8000, which is discussed in more detail in the next section. ----------------------------------------------------------------------- Section 6:5 ARRAYS ----------------------------------------------------------------------- Arrays have two unique properties: 1) they allow you to store large amounts of information with very little overhead and without taking up large portions of your worksheet; and 2) their values can be set with the assignment operators (=, +=, -=, etc..). Once a FORMULA CELL has been created, an ARRAY AREA of upto 8000 values can be defined and attached to it (see Section 4:1 "Array" for more information on how to do this). These values can then be referenced by including the Array Element field in the Cell Address. For example, the Cell Address "[1,2,7]" would reference the 7th array value assigned to the cell at row 1, column 2. An important point to remember is that element position '0' IS NOT part of the ARRAY AREA. This position is reserved for the result of the cell's formula and is assigned to it when the cell is first created. The ARRAY AREA, on the other hand, is defined by the user and can only be used to store static values or numbers. Referencing elements outside of the array area (by using element numbers larger than the maximum size of the array space defined for the cell) will always return a value of zero (0). Because formulas are not and cannot be associated with the values stored in the ARRAY AREA, modifications to these values will not trigger recalculations of any cell that references them; so, exercise caution when referencing arrays that might have their values changed (see Section 6:11 on Forced Recalculations for ways around this). Generally speaking, arrays should be reserved for relatively static data. If necessary, however, array values can modified from either the Input Line (see Chapter 7 - Set Element) or programmatically with the assignment operators (see Section 4:3 - Assignment Operators). When using assignment operators, the Array Element field of the Cell Address MUST be included and it MUST be greater than or equal to 1 and less than or equal to the maximum array space defined for the cell. You can NOT uses an assignment operator to change the value at element position '0'. Remember, this position is reserved for the cell's formula and is not part of the array space. This might best be explained with an example. Let's say you enter the expression "2+2" in cell [1,2]. In this case, the value 4 would be stored at element position '0' and would be displayed on the screen. If you were then to type the expression "[1,2,0]=3" at another cell location, you would in effect be trying to tell cell [1,2] that the result of its formula "2+2" equals 3 - not 4! If, on the other hand, you had entered "[1,2,1]=3" it would have been perfectly legal, since an array element simply stores a value and is not the result of a pre-assigned formula. IMPORTANT: While an Array Area can be assigned to a cell at any time, it is best to do it (especially for large ones) as soon as possible after starting up the program - before the computer's memory becomes fragmented. You should also be careful when moving a cell that contains an array, since other cells that might reference its array elements will not be adjusted to account for the new location. NOTE: As previously mentioned, arrays can only be assigned to FORMULA cells. This, however, does not mean that the Array Element field of LABEL cells is not used. For LABELS, this field is used to address specific characters within a label. For example, if cell [1,3] contained the label "Hello world!", [1,3,1] would address the first letter 'H' (or more specifically, the ASCII value for 'H' which is 72). The assignment operators can also be used to reset the characters of any label to any ASCII value ranging between 32 and 255. Characters with ASCII values greater than 125 will not be saved when backing up your worksheet. Example 1 REFERENCING AN ARRAY ELEMENT [1,2,7] * 10 Example 2 ASSIGNING A VALUE TO AN ARRAY ELEMENT [1,2,7] = 25.0 Example 3 LOADING AN ARRAY AREA WITH VALUES FROM THE WORKSHEET Load a 400 element array, defined at cell [1,50], with a range of values that appear between cells [1,1] and [20,20] on the worksheet (see Section 6:7 "Range Operations" for details on how to interpret this expression). [1,1 ^ 20,20; {[1,50,[#]]=[@]}] NOTE: The array area for cell [1,50] must have been defined prior to making these assignments. ----------------------------------------------------------------------- Section 6:6 MULTIPLE WORKSHEETS ----------------------------------------------------------------------- Up to four (4) worksheets can be loaded and accessed at any given time. You can move between them with the F& (Level) option described in Section 4:6 or with the Forward Slash (/) 'Goto' Command described later on in Chapter 7. The number between the angle brackets <>, located at the intersection of the Row and Column Bars, indicates the worksheet level of each window. Formulas can be written that reference cell values from the same worksheet by leaving the Worksheet Level field BLANK or from worksheets loaded at different levels by including the Worksheet Level field in the Cell Address. For example: [1,2] ==> Addresses the cell at row 1, column 2 of whatever level the formula that contains this reference is loaded at (1 thru 4). [1,2,0,3] ==> Addresses the cell at row 1, column 2, worksheet 3 Normally, it IS NOT a good idea to include the 'Worksheet Level' field in a Cell Address that makes a reference to a cell at the SAME level. First it is not necessary; and second, it can cause problems if you later copy the cell's formula to another level. When this field is left blank or is set to zero (0), it's value will automatically be reset to whatever level it is loaded at! This allows formulas that contain Cell Addresses to be copied to virtually any level without modifying them. Remember, whenever the Worksheet Level field is hardcoded (fixed), you are making an absolute reference to a cell at a particular level. This reference will not be adjusted when the cell is copied or moved to another worksheet level. For example, if a formula entered at level 1 contains a hardcoded cell reference to another cell at level 1 (e.g. [2,3,,1]), it will still reference the same cell (at level 1) - even if the worksheet is later loaded at level 3. So, when in doubt, do not use the 'Worksheet Level' field, since 99 percent of the time your formulas will be referencing other cells within the same worksheet. ----------------------------------------------------------------------- Section 6:7 RANGE OPERATIONS ----------------------------------------------------------------------- Unlike most spreadsheets, that restrict a range to defining a block of cells which can be used in function calls, this spreadsheet allows a range to be used in much the same way as a Cell Address. That is, a range (or Range Operation) IS AN EXPRESSION that returns a value - not just a block of cells. What makes this possible, is the ability to associate one or more formulas to a whole the range of cells. Each of these formulas is evaluated once, except when surrounded by curly braces {}; in which case, the expression is evaluated once for each FORMULA cell that falls within it's range. This allows you to move though a whole series of cells, performing a variety of operations as you go. The following is a diagram of how Range Operations are organized: defines formulas surrounded by lower right curly braces {} are corner ___ executed once for each defines | _ cell within the range upper left ______ |___ | corner | | | | | [row,col ^ row,col ; f; {f}; f;] | | |_______| required ____| | |____ formulas not | surrounded by required between _____| {} are evaluated each formula only once [ ] - By definition, Range Operations MUST be enclosed within square brackets, indicating they return a value. ^ - The (^) symbol is a required to separate the cells which define the upper left and lower right corners of the range of cells. f - The (f) in the above diagram represents an expression (formula) that is evaluated once when it is first encountered. {f} - Curly brackets {} act like a FOR or DO loop that forces the Range Operation to access the value of each FORMULA cell within the range (beginning at the upper left corner and moving down in a left-right direction). The formula within the brackets is executed once as each cell is encountered. Only one formula can be enclosed within each set of brackets. ; - Each formula must be separated by a semicolon. There are also seven special 'registers' that are reserved especially for Range Operations. These registers are designed to track critical values that may change as the expressions (associated with the Range Operations) are evaluated. Each is represented by a special character enclosed within square brackets (i.e. [$], [#], etc..) and is described below. [$] - Contains the INTERMEDIATE and FINAL results of a Range Operation. It can act as an accumulator, whenever an expression is surrounded by curly braces {}, storing the value produced by the expression after it is evaluated once for each cell within the range. By default, this register is initialized to '0' prior to evaluating any expression that is surrounded by curly braces. [@] - Whenever an expression is enclosed within curly brackets {}, it will be evaluated once for every FORMULA cell within the range. The [@] register acts like a variable Cell Address that contains the VALUE of the cell currently being accessed by the Range Operation as it moves from one cell to the next. [#] - This register is a COUNTER. It is initialized to one (1) at the start of any expression that is surrounded by curly brackets {}. Its value is incremented by one each time the Range Operation moves to the next FORMULA cell. [*] - This register has no predefined function. It can be used with assignment operations to hold the intermediate results of any calculation. [-] - Whenever an expression is enclosed within curly brackets {}, it will be evaluated once for every FORMULA cell within the range. The [-] register contains the ROW number of the cell currently being accessed by the Range Operation, which is the same cell whose value is currently loaded in the [@] register. [|] - This register contains the COLUMN number of the cell currently being accessed by the Range Operation. [%] - This register contains the WORKSHEET LEVEL number of the cell currently being accessed by the Range Operation. Example 1 COUNT THE CELLS WITHIN A RANGE Count the number of FORMULA cells within a range. (Empty cells and cells that contain Labels are skipped.) [1,1 ^ 12,12] NOTE: By default, a Range Operation that contains no formulas will return the total number of FORMULA cells found within the range. A more literal way of doing the same thing would be: [1,1 ^ 12,12; [$]=[#] ] REMEMBER: The result of any expression, after it has been evaluated (in this case: [#]) will automatically be placed in the [$] register. As a result, the [$]=[#] assignment really isn't necessary and the above Range Operation could have been shortened to look like this: [1,1 ^ 12,12; [#] ] Example 2 SUM ALL OF THE CELLS WITHIN A RANGE ___ accumulated results | [1,1 ^ 12,12; {[$]+[@]} ] |___ value of current cell being accessed Example 3 SUM THE VALUES WITHIN A CELL'S ARRAY AREA ___ accumulated results | [2,3,1 ^ 2,3,12; {[$]+[@]} ] | | |__ value in array element of | | cell being referenced |________|___ array element numbers NOTE: Range Operations can only be performed on arrays if the Row, Column, and Worksheet Level are equal. Example 4 COMPUTE AVERAGE VALUE WITHIN A RANGE accumulated results___ | [1,1 ^ 12,12; {[$]+[@]}; [$]/[#] ] | final cell count__| REMEMBER: Only FORMULA cells are visible to a Range Operation. Example 5 RETURN THE LARGEST NUMBER WITHIN A RANGE Find the largest positive number within the range bounded by cell [10,10] in the upper left hand corner and cell [20,20] in the lower right corner. [10,10^20,20; {([@]>[$]) ?[@] :[$];} ] Example 6 STANDARD DEVIATION Compute the Standard Deviation for a range of cells between [1,1] and [5,1]. save average ____ | [1,1 ^ 5,1; {[$]+[@]}; [*]=[$]/[#]; {[$]+([@]-[*])**2}; sqrt(([$])/([#]-1))] Example 7 SEARCH A RANGE FOR A VALUE Perform a Table Search between cells [1,1] and [5,1] for the first value greater than 4. If found, return the value located one column to the right of it. (This is similar to the @VLOOKUP function used by many spreadsheets). [1,1 ^ 5,1; {[*] = ([@]>4) ?[[-],[|]+1] :[*];}; [$]=[*]] ----------------------------------------------------------------------- Section 6:8 DIRECT CELL ADDRESSES ----------------------------------------------------------------------- A Cell Address (i.e. [Row,Col,Element,Level]) makes a 'direct' cell reference to the value in another cell when: NONE OF THE FIELDS USED WITHIN THE CELL ADDRESS ARE VARIABLE. That is, when the specific cell that is being referenced by a Cell Address (within a formula) can never be changed as the result of an update made somewhere else in the spreadsheet, it is termed a 'direct' cell reference. For example, "[1,2]" makes a direct cell reference to the cell at row 1 column 2, since it's Row or Column fields can not vary. Direct cell references have one very important property - THEY CAN CAUSE THE FORMULAS THAT CONTAIN THEM TO BE RECOMPUTED WHENEVER THE VALUE OF THE CELL BEING ADDRESSED IS CHANGED. This is opposed to expressions that contain 'indirect' cell references (see Section 6:9), which are not recomputed under similar circumstances. ----------------------------------------------------------------------- Section 6:9 INDIRECT CELL ADDRESSES ----------------------------------------------------------------------- Because the 'Row', 'Column', 'Array', and 'Worksheet' fields are themselves expressions, their values can be designed to vary based on a change made to the spreadsheet. When this condition exists, it has the affect of changing the actual cell being addressed! This is called 'indirect' cell addressing, and it can be quite useful at times. Say, for example, cell [1,1] contains the value 10 and cell [2,2] contains the value 20. A Cell Address can now be written that uses the values in both of these cells as its 'row' and 'column' number fields; thereby referencing cell [10,20] - INDIRECTLY! In other words, the cell that is actually being addressed depends on the contents of cells [1,1] and [2,2]. It might look something like this: __________________ Indirect Reference | | to cell [10,20] [ [1,1] , [2,2] ] The ROW is the value | | The COLUMN is the value in cell [1,1]________| |____ value in cell [2,2] NOTE: In the above example, the references to cells [1,1] and [2,2] are both 'direct' cell references. Notice that as the values in cells [1,1] and/or [2,2] change, the 'address' of the cell being referenced by the above expression will also change. This can be a very powerful means of accessing values within tables, depending on the results of other calculations. There is, however, one significant trade off you must make when using 'indirect' cell references: A FORMULA THAT CONTAINS AN 'INDIRECT' REFERENCE TO A CELL, WILL NOT BE RECALCULATED WHEN THE VALUE OF THAT CELL IS CHANGED. The only way to get around this particular drawback is to set a flag that will FORCE the cell, containing the reference, to be recalculated each time ANY change is made to the spreadsheet (see Section 6:11 - Forced Recalculations). ----------------------------------------------------------------------- Section 6:10 WHEN DO FORMULAS GET RECALCULATED ? ----------------------------------------------------------------------- Sometimes it's difficult to tell which cells will be affected, when a change is made to the worksheet. If you're having trouble with this, try to remember the following rules: A DIRECT CELL REFERENCE, within a formula, will cause THAT formula to be recalculated each time the value of the cell (being referenced) is changed. ALL formulas that contain Range Operations are recomputed at least once - each time a change is made to the worksheet (unless the 'dRange' value is set to zero - see Section 4:8). The (~) Cell Trace Command can also be used to locate the cells that make 'direct' references to other cells (see Chapter 7). This is done by placing the Cell Pointer over the cell in question and executing the command from the Input Line. You will then be shown all of the cells that reference it. These are the cells that will cause the formula to be recomputed if their values change. You will be also shown the cells that USE the value of the cell in question. ----------------------------------------------------------------------- Section 6:11 FORCING CELLS TO RECALCULATE ----------------------------------------------------------------------- Occasionally, you may run into a case were a cell's formula is not recalculate when the value of one of the cells that it references is changed. For example, if a cell makes an INDIRECT CELL REFERENCE to another cell, its formula will NOT be recomputed when the value of this (indirectly) referenced cell is changed. When it is absolutely necessary that a cell's value reflect such changes, a special flag can be set that will FORCE the cell's formula to be recalculated whenever ANY change is made to the spreadsheet. Section 4:1 "+ReCalc" describes how to do this by setting up and executing the following command: "+ReCalc Cell OKAY?". This flag should be used sparingly, however, as it defeats the "Minimal Re-Calc" capabilities of the spreadsheet. It is also up to you to keep track of the cells that have had their 'ReCalc' flag set. ----------------------------------------------------------------------- Section 6:12 OPTIMIZING YOUR WORKSHEET ----------------------------------------------------------------------- Generally speaking, there are several things you can do to optimize your worksheet: 1) PREVENT complex formulas from being recomputed until their results are needed by setting the "+NoCalc" flag (see Section 4:1); 2) LIMIT the number of 'indirect' cell references - 'direct' cell references are faster; 3) KEEP the 'dRange' default setting set to 1 (refer to Section 4:8) to reduce the number of times a formula that contains a recursive Range Operation will be recalculate. If a cell contains a complicated formula that takes a long time to re- calculate, you can speed up the spreadsheet by blocking the calculation until you the specifically request that it be made. Section 4:1 describes how to do this by setting up and executing the following command: "+NoCalc Cell OKAY?". It's up to you, however, to remember when and where you have used this flag on a cell! To minimize the number of calculations that must be performed after a change has been made to the spreadsheet, REBEL will only re-compute the cells that are affected by the change. This is often referred to as "minimal recalc". There are, however, certain cases in which this strategy can be more of a disadvantage than an advantage. Take, for example, cell [25,25] whose formula contains a 'Range Operation' that sums all of the cells bounded by cell [1,1] (in the upper left corner) and cell [20,20] (in the lower left corner). The expression might look something like this: [1,1 ^ 20,20; {[$]+[@]}]. Here's the problem. Lets say that the value of EACH cell within this range is affected by a change made to cell [45,45] (i.e. all cells within this range make a 'direct' reference to cell [45,45]). Now, imagine the following scenario. Suppose you change the value in cell [45,45]. What happens? Each of the cells within the range will be recomputed; AND EACH TIME A CHANGE IS MADE TO A ONE OF THESE CELLS IT WILL TRIGGER THE CELL THAT CONTAINS THE 'RANGE OPERATION' TO BE RECOMPUTED - cell [25,25]. As a result, cell [25,25] will be needlessly recomputed 400 times (once for each cell in the range); when, in reality, the Range Operation only needed to be recomputed once, after the last cell in the range was updated. This, of course, is a worst case and is easily handled with a special setting that can limit the number of times a Range Operation can be be recalculated as the result of a single change made to the worksheet (see Section 4:8 - "dRange"). Another type of Range Operation that can cause problems is one that includes itself WITHIN ITS OWN RANGE! That is, a Range Operation that makes a circular reference to itself, causing the cell's formula to be recalculated - indefinitely. Once again, the "dRange" setting can be used to control this situation. ----------------------------------------------------------------------- Section 6:13 MISCELLANEOUS TOPICS ----------------------------------------------------------------------- ENTERING NUMBERS (from the Input Line) Numeric values can be entered from the Input Line in several different ways. For example: 1) Standard decimal values can be entered simply by typing them as they appear: 0 ===> 0 25.4 ===> 25.4 40 ===> 40 040 ===> 32 (CAUTION! do not precede a decimal value with a '0', unless the number is zero.) 2) Octal numbers can be entered by beginning them with a zero: 040 ==(octal equivalent)==> 32 0377 ==(octal equivalent)==> 255 3) Hexadecimal numbers can be entered by preceding them with a "0x": 0xFF ===(hex equivalent)===> 255 4) The numeric value of any ASCII character can be entered by enclosing the character within single quotes. Note, if the single quote is the first character in the expression, be sure the precede it with a plus sign (+'A'). This will prevent the expression from being interpreted as a LABEL. 'A' ==(ascii equivalent)==> 65 'B' ==(ascii equivalent)==> 66 '!' ==(ascii equivalent)==> 33 IMPORTANT! To avoid confusion, remember to NEVER being a decimal number with a zero (unless, of course, the number is zero)! SWITCHES Several switches can be used when starting up REBEL: 1) The -S switch can be used to create a backup file, whenever a worksheet is load. This file will be an exact copy of the original worksheet, but will have a .RB0 extension. (You will have to rename it to a .RB@ file before you can use it.) 2) The -B switch can be used to startup REBEL in black and white mode; otherwise, the program will be be started in the color mode. 3) The -L: option is used to 'attach' a script library to the current session. The -L: switch should be followed immediately by the name of the library file (no blanks). Also refer to Section 11:2. REBEL -L:STDLIB 4) The -M: option is used to set the size of the Swap Area used by the Script Manager. The -M: switch should be followed immediately by the size (in bytes). Refer to Section 11:4. REBEL -L:STDLIB -M:2000 5) The -X: option is used to executed a script immediately upon entering the spreadsheet. This option is useful when it's important to hide the spreadsheet and it's data from the user. The option is used by following the -X: portion of the switch immediately with the name of the script you wish to execute. Remember, you must also use the -L: option to attach the library that contains the script when using this option. REBEL -L:MYLIB -X:scriptname RESERVED and WILDCARD CHARACTERS The tilde (~) character is reserved by REBEL as a wildcard character. As a result, it can not be used within a label. (Refer to the 'Search' command in the next Chapter, and the 'Load' and 'ListDir' commands described in Section 4:3 for examples of its uses.) ________________________ _____________________________________________/ Chapter 7 SHORTCUTS The following 'Shortcut' commands are designed to be executed from the Input Line (below the Function Key Menu). They do not perform critical tasks; although, you may find them useful time savers. Each of these commands (with the exception of the <Tab> Command) must be followed by the <Enter> key before it is executed. NOTE! Because the backslash (\), period (.), and equal sign (=) are used to trigger Shortcut Commands, you CANNOT begin a LABEL with one of these characters without first preceding it with a single quote ('). Command Description CHANGE WINDOWS <Tab> Pressing the <Tab> key at the Input Line prompt, will cause the Cell Pointer to jump to another window (if one exists). SEARCH (\) A backslash (\) followed by any pattern of characters will cause the Cell Pointer to move to the first cell the contains an occurrence of that pattern. If the Cell Pointer is positioned on an 'empty' cell, the search will begin at the top of the worksheet. If the Cell Pointer is positioned on an 'occupied' cell, the search will begin from that point. The pattern does not have to be re-entered to make repeated searches. Simply type a backslash (\) followed by the <Enter> key after you have entered the pattern the first time. The wildcard character (~) can also be included within the search pattern. GOTO CELL (/) A Forward Slash (/) will produce a series of prompts that will request the Row, Column, and Worksheet Level to move the Cell Pointer to. The value in the square brackets [] of each of these prompts is the default answer that will be used if you press the <Enter> key with no other input. This command can also be used with prompts that require you to move the Cell Pointer to define a Range or Target cell. ELEMENT DISPLAY (=) An Equal Sign (=) followed by an array element number (e.g. =5) can be used to display the value of any array position of the cell highlighted by the Cell Pointer. The display appears at the Input Line and can be removed by pressing any key. For example, "=1" will display the value of the first array element. Note: "=0" will always return the value associated with the cell's formula. The following variations of this command are also acceptable: =* Displays all of the values stored in a cell's array area - one at a time. = The equal sign (alone), works like the "=*' variation, except when displaying the contents of cells that contain coordinates or matrices. In these case, coordinate pairs and matrix rows are displayed together (if possible). =(5,9) Displays elements 5 thru 9 SET ELEMENT (=e<value) An Equal sign (=) followed by an array element number (greater than 0), then a Less Than sign (<), and finally a value will cause that value (to the right of the '<' sign) to be loaded at the designated array position. For example, "=7<25" will load the value 25 into element position 7 of the cell that is highlighted by the Cell Pointer. The following variations of this command may also be used: =*<0 Set the values of all elements to 0 =(5,9)<0 Set elements 5 thru 9 to 0 CELL RECALC (.) The formula of any cell can be quickly RECALCULATED by positioning the Cell Pointer over the cell and entering a Dot (.) followed by the <Enter> key. ________________________ _____________________________________________/ Chapter 8 FUNCTIONS Index to Functions abs(x) . . . . . . 8:1:1 MATH . . . . . . absolute value acos(x) . . . . . 8:2:5 TRIG . . . . . . arccosine asin(x) . . . . . 8:2:6 TRIG . . . . . . arcsine atan(x) . . . . 8:2:7 TRIG . . . . . . arctangent atan2(x,y) . . . . 8:2:8 TRIG . . . . . . arctangent atof . . . . 8:3:1 STRING . . . . . string to number ceil(x) . . . . 8:1:2 MATH . . . . . . ceiling col(x) . . . . 8:4:1 MISC . . . . . . column-offset cos(x) . . . . 8:2:1 TRIG . . . . . . cosine cotan(x) . . . . 8:2:2 TRIG . . . . . . cotangent exp . . . . 8:1:3 MATH . . . . . . exponential floor . . . . 8:1:4 MATH . . . . . . floor frac . . . . 8:1:5 MATH . . . . . . fractional value index . . . . 8:3:2 STRING . . . . . locate character int . . . . 8:1:6 MATH . . . . . . integer value log10 . . . . 8:1:7 MATH . . . . . . base 10 log ln . . . . 8:1:8 MATH . . . . . . natural log lvl . . . . 8:4:3 MISC . . . . . . worksheet level offset mode . . . . 8:1:9 MATH . . . . . . change mode of angle rnd . . . . 8:1:10 MATH . . . . . . rounds value row . . . . 8:4:2 MISC . . . . . . row offset sin . . . . 8:2:3 TRIG . . . . . . sine sqrt . . . . 8:1:11 MATH . . . . . . square root strcat . . . . 8:3:3 STRING . . . . . string concatenate strcmp . . . . 8:3:5 STRING . . . . . string compare strcpy . . . . 8:3:7 STRING . . . . . string copy strlen . . . . 8:3:9 STRING . . . . . string length strncat . . . . 8:3:4 STRING . . . . . concatenate n chars strncmp . . . . 8:3:6 STRING . . . . . compare n chars strncpy . . . . 8:3:8 STRING . . . . . copy n chars tan . . . . 8:2:4 TRIG . . . . . . tangent NOTE: Refer to Chapters 9 and 10 for COGO and MATRIX functions. Also see file STDLIB.DOC script documentation. IMPORTANT: All of the functions listed in this Chapter are case sensitive (i.e. spell them just as they appear - in most cases, with lower case letters). In addition, remember to use a '+' sign whenever a function begins a formula. This will insure that the cell is interpreted as a FORMULA rather than a LABEL. ----------------------------------------------------------------------- Section 8:1:0 MATH FUNCTIONS ----------------------------------------------------------------------- 9:1:1 abs (x) Returns the absolute value of any expression 'x'. 8:1:2 ceil (x) Returns the smallest integral value that is greater than or equal to the value produced by expression 'x'. 8:1:3 exp (x) Returns the exponential function of any expression 'x'. 8:1:4 floor (x) Returns the largest integral value that is less than or equal to the value produced by expression 'x'. 8:1:5 frac (x) Returns the fraction part of 'x'. 8:1:6 int (x) Returns the integer part of 'x'. 8:1:7 ln (x) Returns the natural logarithm of any expression 'x'. An error is returned for values of 'x' that are less than or equal to 0. 8:1:8 log10 (x) Returns the logarithm to the base 10 of any expression 'x'. An error is returned for values of 'x' that are less than or equal to 0. 8:1:9 mode (frommode,tomode,angle) Changes the mode of 'angle' to another mode. The mode settings are: 0=radians, 1=decimal degrees, 2=grads, and 3=degrees/minutes/seconds (DDD.MMSSss). The 'frommode' parameter is setting to the current mode of the angle and the 'tomode' parameter is set to the return mode. Example: +mode(1,3,90.50) = 90.3000 8:1:10 rnd (x,n) Rounds 'x' to 'n' decimal places, where 'n' can range between 15 and -15. If 'n' is negative, it rounds to the n-th power of 10 (e.g. round(1891,-2) = 1900). 8:1:11 sqrt (x) = square_root Returns the square root of any expression 'x'. An error is returned for values of 'x' that are less than 0. ----------------------------------------------------------------------- Section 8:2:0 TRIG FUNCTIONS ----------------------------------------------------------------------- 8:2:1 cos (angle) = cos OPTIONAL FORM: cos (angle,mode) 8:2:2 cotan (angle) = cotan OPTIONAL FORM: cotan (angle,mode) 8:2:3 sin (angle) = sin OPTIONAL FORM: sin (angle,mode) 8:2:4 tan (angle) = tan OPTIONAL FORM: tan (angle,mode) Returns the sin, cos, tan, or cotan of any 'angle' in radians. Angles in degrees and grads can also be used by including the optional 'mode' parameter, where 0=RADIANS (default), 1=DEGREES, and 2=GRADS. 8:2:5 acos (cos) = angle OPTIONAL FORM: acos (cos,mode) 8:2:6 asin (sin) = angle OPTIONAL FORM: asin (sin,mode) 8:2:7 atan (tan) = angle OPTIONAL FORM: atan (tan,mode) 8:2:8 atan2 (x,y) = angle OPTIONAL FORM: atan2 (x,y,mode) Returns the arc sin, arc cos or arc tan in radians. Angles can also be returned in degrees and grads by including the optional 'mode' parameter, where 0=RADIANS (default), 1=DEGREES, and 2=GRADS. ----------------------------------------------------------------------- Section 8:3:0 STRING FUNCTIONS ----------------------------------------------------------------------- 8:3:1 atof (label) = value Converts a character string to it numeric equivalent. Examples: atof ("123.55") = 123.55 8:3:2 index (label,c) = pos OPTIONAL FORM: index(label,c,start) Returns the 'position' of the first occurrence of a character 'c' in a 'label'. The search can be optionally started from any location by including that location in the optional 3rd parameter. Examples: index ("Hello World!",'o') = 5 index ([1,1],'o',6) = 8 8:3:3 strcat (cell,label) = errorcode Appends the contents of a 'label' or a 'string' to another 'cell'. Returns '0' if successful. Note, this function WILL NOT cause other cells to be recalculated as a result of any change to the cell in the first parameter. Examples: strcat([1,1],"Hello World!") strcat([1,1],[1,3]) 8:3:4 strncat (cell,string,n) = errorcode Appends the first 'n' characters from a 'string' to the label of to another 'cell'. Returns '0' if successful. Note, this function WILL NOT cause other cells to be recalculated as a result of any change to the cell in the first parameter. Examples: strncat([1,1],"Hello World!",5) strcat([1,1],[1,3],5) 8:3:5 strcmp (label1,label2) = status Compares 'label1' and 'label2', returning: (1) if 'label1' is GREATER than 'label2' (0) if 'label1' is EQUAL to 'label2' (-1) if 'label1' is LESS the 'label2' Examples: strcmp ([1,1],"Hello World!") strcmp ([1,1],[1,3]) 8:3:6 strncmp (label1,label2,n) = status Compares the first 'n' characters of 'label1' and 'label2', returning: (1) if 'label1' is GREATER than 'label2' (0) if 'label1' is EQUAL to 'label2' (-1) if 'label1' is LESS the 'label2' Examples: strncmp ([1,1],"Hello World!",5) strncmp ([1,1],[1,3],5) strlen ([1,1]) 8:3:7 strcpy (cell,string) = errorcode Copies the contents of a cell or of a 'string' to another 'cell'. Returns '0' if successful. Note, this function WILL NOT cause other cells to be recalculated as a result of any change to the cell in the first parameter. Examples: strcpy ([1,1],"Hello World!") strcpy ([1,1],[1,3]) 8:3:8 strncpy (cell,string,n) = errorcode Copies the first 'n' characters of a 'string' to another 'cell'. Returns '0' if successful. Note, this this function WILL NOT cause other cells to be recalculated as a result of any change to the cell in the first parameter. Examples: strncpy ([1,1],"Hello World!",5) strncpy ([1,1],[1,3],5) 8:3:9 strlen (label) = number_of_characters Returns the number of characters in a label (string). Examples: strlen ("Hello World!") ----------------------------------------------------------------------- Section 8:4:0 MISC FUNCTIONS ----------------------------------------------------------------------- 8:4:1 col (offset) = column+offset Adds the 'offset' value to the current COLUMN. 8:4:2 row (offset) = row+offset Adds the 'offset' value to the current ROW. 8:4:3 lvl (offset) = level+offset Adds the 'offset' value to the current worksheet LEVEL. _____________________________ ________________________________________/ Chapter 9 COGO Chapter Contents Section 9:1 ABOUT THE FUNCTIONS USED IN THIS PACKAGE Section 9:2 ABOUT THE PARAMETERS USED BY COGO FUNCTIONS 9:2:1 Referencing other coordinates 9:2:2 Setting the angular mode (radians, degrees, grads) 9:2:3 Setting the angle type (ray, azimuth, bearing, etc..) 9:2:4 Entering angles (ray, azimuth, bearing, etc..) 9:2:5 Setting the display format Section 9:3 FUNCTIONS THAT RETURN A SINGLE COORDINATE 9:3:1 @XY . . . . . . (Load (x,y) Rec Coordinate) 9:3:2 @NE . . . . . . (Load (north,east) Rec Coordinate) 9:3:3 @POLAR. . . . . (Load Polar Coordinate (radius,ray)) 9:3:4 @Traverse . . . (Bearing/Azimuth Traverse) 9:3:5 @FieldAngle . . (Field Angle Traverse) 9:3:6 @AngAngInt . . (Angle/Angle Intersection) 9:3:7 @AngDstInt . . (Angle/Distance Intersection) 9:3:8 @DstDstInt . . (Distance/Distance Intersection) 9:3:9 @CurveRadius . (Horizontal Curve with Radius) 9:3:10 @CurveDelta . . (Horizontal Curve with Delta Angle) Section 9:4 FUNCTIONS THAT RETURN MORE THAN ONE COORDINATE 9:4:1 @Segment . . . (Line Segment) 9:4:2 @Polygon . . . (Closed Polygon/Traverse) Section 9:5 FUNCTIONS THAT RETURN AN ANGLE and DISTANCE 9:5:1 @Inverse . . . (Inverse Traverse) 9:5:2 @Radial . . . . (Radial Stakeout) 9:5:3 @Closure . . . (Error of Closure/Area of POLYGON) Section 9:6 FUNCTIONS THAT RETURN MULTIPLE VALUES 9:6:1 @Triangle . . . (Triangle Solution) Section 9:7 NULL POINTS, SEGMENTS, and POLYGONS Section 9:8 GRAPHICS Section 9:9 EXAMPLES ------------------------------------------------------------------------ Section 9:1 ABOUT THE FUNCTIONS USED IN THIS PACKAGE ------------------------------------------------------------------------ While this package is easy to use, it does take advantage of several of REBEL's more advanced features. As a result, it is NOT advisable to jump immediately into these functions without first reviewing the preceding chapters. I highly recommend, that at a very minimum, you become familiar with the concept of "Cell Array Areas" (discussed in Section 6:5) before continuing. In general, you'll find that most of the functions described in this chapter work alike, which makes them easy to learn. There are, however, a few important differences that distinguish these functions from the ones you have been exposed to thus far. The most significant of these differences is the way in which values are returned. Up to now, functions and expressions have always returned a single value which is displayed by the cell. Coordinate Geometry functions, on the other hand, return (and sometimes display) multiple values, such as a coordinate pair. The '@' symbol that precedes the name of each of these functions is designed to help remind you of this fact. For most spreadsheets, dealing with complex numbers such as this presents a major problem, since they are only capable of storing a single value in each cell. This is not a limitation for REBEL, however, which can dynamically create and load upto 8000 values in the "array area" of a cell. Before attempting to use these functions, there are a few additional facts you should be aware of. FIRST, as just mentioned, all '@' functions return multiple values, which are automatically loaded into the cell's array area for you. It is important to remember that before a cell can be recognized by other functions as containing one of these multiple value solutions (such as a coordinate pair), the cell MUST have been created with an '@' function. In other words, if you want to enter a known or fixed coordinate pair, you must use either the @XY, @NE, or @POLAR function to do it. You can not create the cell yourself simply by defining and loading an array area. Null Points, Segments, and Polygons (discussed in Section 9:7) are the one exception to these rule. SECOND, no '@' function can be nested within the parameter list of another function or used within an expression. These functions must be the lone entry in a cell's formula. THIRD, '@' functions rarely display the value stored at element position [0], which is normally the case with expressions and standard functions that return single values. Instead, the '@' functions typically display one or more of the values stored in the cell's ARRAY AREA, such as a coordinate pair or an anlge and distance. As a result, the Column Width must be set wide enough to display multiple values - to prevent an overflow. If this condition (an overflow) should occur, a series of stars (*****) will be displayed by the cell. To correct it, simply increase the Column Width until the correct values appear (refer to the 'Width' Menu option in Section 4:6). FOURTH, many '@' functions return valuable secondary information that is not displayed. These values, which are also stored in the cell's array area, are defined at the end of each function description and may be inspected using the "=*" Element Display command discussed in Chapter 7. SUMMARY 1) YOU CAN NOT CREATE A 'POINT', 'SEGMENT', OR 'POLYGON' CELL WITHOUT USING AN '@' FUNCTION TO DO IT. That is, simply loading the array area of a standard FORMULA cell with the correct values will not work (refer to Section 9:7 for the one exception to this rule). 2) AN '@' FUNCTION CAN NOT BE NESTED (USED AS A PARAMETER) OR USED WITHIN AN EXPRESSION - THEY MUST STAND ALONE. 3) IF THE WIDTH OF A CELL IS NOT SUFFICIENT TO DISPLAY THE RESULTS OF A '@' FUNCTION, A SERIES OF STARS (****) WILL BE DISPLAYED, INDICATING A DISPLAY OVERFLOW. 4) ALL FUNCTIONS SHOULD BE ENTERED EXACTLY AS THEY APPEAR IN THIS MANUAL. FUNCTION NAMES ARE CASE SENSITIVE. For example, "@Traverse" should NOT be entered as: "@traverse" or "@TRAVERSE". ------------------------------------------------------------------------ Section 9:2 ABOUT THE PARAMETERS USED BY COGO FUNCTIONS ------------------------------------------------------------------------ The functions used in this package contains parameters that allow them to be customized to fit your particular needs. For example, if you prefer to work in degrees as opposed to radians, you can do it by setting the appropriate parameter value. Since many of the functions require the same parameter entries, learning them is easy once you get started. To keep from repeating myself a dozen times, however, I have decided to explain the parameters that are common to more than one function only once in the next five sections (Sections 9:2:1 thru 9:2:5). At first, you may feel somewhat overwhelmed by the number of options that are available. I have tried to balance the power and flexibility that these parameters provide with the ease-of-use of the functions in general - without sacrificing to much in the process. I realize there is often a fine line between these objectives (I only hope I have succeeded in maintaining a balance). It may help to mark the specific options you are interested in as you read through each section, which are really the only ones you need to remember. The remaining options can be ignored until you need them. The following diagram provides an example of each parameter type and the section number it is discussed in: (Section 9:2:1) (9:2:3) (Section 9:2:5) | | | @Traverse ([setup],mode,type,angle,distance,fmt) | | (Section 9:2:2) (9:2:4) 9:2:1 REFERENCING OTHER COORDINATES @Traverse ([SETUP],mode,type,angle,distance,fmt) ^ Whenever you see a parameter that is enclosed within Square Brackets [], it takes on special meaning. A parameter of this type MUST be the Cell Address ([row,col]) of a cell that contains a POINT, SEGMENT or POLYGON (depending on the function's requirements). If you attempt to address a cell that does not contain the proper cell type, an error will be returned. In the above example, '[setup]' represents the cell that contains the coordinate from which the 'angle' and 'distance' are measured. Note: The @XY, @NE, and @POLAR functions provide the easiest means of loading a cell with the initial coordinate from which other coordinates are computed. This 'initial' point is normally known and therefore must be loaded directly before it can be reference by other '@' functions. 9:2:2 SETTING THE ANGLE MODE (radians, degrees, grads) @Traverse ([setup],MODE,type,angle,distance,fmt) ^ The 'mode' parameter tells the spreadsheet what unit of measure to assume for all angles used in the function's parameter list. Four angular modes (0-3) are available: mode=0 Radians mode=1 Degrees (decimal) DDD.ddd mode=2 Grads mode=3 Degrees (degrees,minutes,seconds) DDD.MMSSss 9:2:3 SETTING THE ANGLE TYPE @Traverse ([setup],mode,TYPE,angle,distance,fmt) ^ The 'type' parameter tells the spreadsheet what kind of angle (bearing, azimuth, ray, etc..) to assume for the angles that are used or returned by the function. There are nine possible settings. The first five (0-4) identify a group of Direction Angles, which define the absolute direction of a line relative to the xy-axis. The remaining four settings (5-8) identify Field Angles that require a 'backsight' coordinate to establish their relative direction. Direction Angle settings (0-4): type=0 Ray (counter-clockwise about the +x axis) type=1 Bearing (90 degrees about the (+/-) y axis) type=2 Azimuth No (clockwise about the +y axis) type=3 Azimuth So (clockwise about the -y axis) type=4 Rotation (180 deg - either direction of +x axis) (0) (1) (2) (3) (4) | 360 90<|>90 |>360 | | 180 ---+--^- ----+---- ----+--- ----+---- ----+--+- | 90<|>90 | 360<| | 180 Ray Bearing Azimuth Azimuth Rotation (north) (south) Field Angles settings (5-8): type=5 Angle Right (right of the backsight) type=6 Angle Left (left of backsight) type=7 Deflection Right (right from line 180d of backsight) type=8 Deflection Left (left from line 180d of backsight) (point measured to) \ (backsight) - - - >\ 6 | 6 / | \<- - |- - -/ 8 |7 \<- -|- - / - - | - ->\ | / | | 5| \ | /-- |8 | | setup | / | | |-----|--------+-----|---|---- | | /| 5| | | 5| / | | |8 | - - -/- |- - - | |7 / | | | /- - |- - - - - | 7 / 8 | 8 - - - -/ | / (line - 180 degrees from backsight) 9:2:4 ENTERING ANGLES @Traverse ([setup],mode,type,ANGLE,distance,fmt) ^ If you used one of the first three angle 'mode' settings (0 thru 2), the angle must be entered using a decimal format. For example, mode (1) angles are entered as follows: DDD.dddd, where the 'dddd' represents decimal degrees. Mode (3) angles, on the other hand, require angles to be entered in degrees, minute, and seconds (DDD.MMSSss) where: 'DDD' denotes whole degrees; 'MM' denotes minutes; 'SS' denotes seconds; and 'ss' denotes decimal seconds. As an example, 75 30'20.95" would be entered: 75.302095. Bearing angles (i.e. mode=3, type=1) are the only angles that require a somewhat unusual entry method, since both the quadrant and the the angle itself must be entered as a single value. This is done by adding a special value to the bearing angle itself to identifies its quadrant. The following diagram illustrates these values: (NW) +400 | +100 (NE) ------+------ (SW) +300 | +200 (SE) Note: Since bearing angles can not be greater than 90 degrees, adding the above values will not affect the angle itself and can be otherwise ignored - except for identifying the quadrant. For example, to represent the bearing N45 30'20"W , you would enter: 445.3020 (i.e. 45.3020 + 400 ). 9:2:5 SETTING THE DISPLAY FORMAT @Traverse ([setup],mode,type,angle,distance,FMT) ^ As mentioned earlier, the '@' functions usually return multiple values, which generally fall into two categories: Rectangular/Polar Coordinates and Angle/Distance pairs. The 'fmt' parameter is used to control how the spreadsheet displays these values. Several options in each category are available. RECTANGULAR/POLAR coordinate display formats: fmt=0 XY Rectangular Coordinate format: "(x, y)" fmt=1 North/East Coordinate format: "[north, east]" fmt=2 Polar Coordinate display format: "{radius, ray}" Notice the brackets used to enclose each coordinate type. They'll help you distinguish between them when displayed. Also, note that when using the Polar Coordinate display format (fmt=2), both the entry and return mode of the 'ray' angle is established by the function's 'mode' parameter. Keep in mind that no matter which display format is used, the coordinate is always stored by the spreadsheet in the same way (i.e. as a rectangular coordinate). The x_value is stored in element position [1] and the y_value is stored in element position [2]. The display format does not affect the actual values stored by the cell. ANGLE/DISTANCE display formats: fmt=0 General display format: TYPE(mode) ANGLE, DISTANCE Example: "BRG(dms) 445.3020, 100.00" Note: This format can be used to display any angle type/mode combination. fmt=1 Bearing/Distance format: Example: "N45 30'20.0"W 100.00" Note: When using this display format, the function's 'mode' and 'type' settings will be ignored and the angle will always be displayed in degrees, minutes and seconds. ------------------------------------------------------------------------ Section 9:3 FUNCTIONS THAT RETURN: RECTANGULAR/POLAR COORDINATES ------------------------------------------------------------------------ All of the functions, described in Sections 9:3, return coordinate pairs. The first three of these functions (@XY, @NE, and @POLAR) are unique in that they do not COMPUTE coordinates; rather, they are designed to LOAD a fixed or known coordinate. Without these functions, it would be impossible to establish the initial point from which other coordinates are computed. Remember, you can not load a coordinate pair into a cell's array area yourself (Section 9:7 discusses the one exception to this case). If an error is detected by any of these functions, a series of question marks (?????) will appear in the cell's display. You can identify the exact problem by moving the Cell Pointer to this cell and then entering the Edit Mode. This will cause an error message to be displayed that will hopefully help you correct the problem (refer back to Chapter 5). 9:3:1 @XY (X_coordinate,Y_coordinate) This function is designed to allow you to load a fixed or known (x,y) coordinate pair into a cell. By default, the coordinate is also displayed using this format. Keep in mind, this function does not 'compute' anything - it only loads a cell with a known coordinate pair. The function is used primarily to establish an INITIAL point from which other coordinates can be computed. Return Contents of Array Area: [0]: 1 (number of coordinates pairs) [1]: x-coordinate value of point [2]: y-coordinate value of point NOTE: Refer to Section 9:7 for a details on how to use this function without a parameter list. 9:3:2 @NE (Northing,Easting) This function is identical to the @XY routine, except that it loads and displays a coordinate using a (North,East) or (y,x) format. The coordinate, however, is stored internally in a (x,y) format, allowing it to be used interchangeably with any coordinate computed by this package. Return Contents of Array Area: [0]: 1 (number of coordinates pairs) [1]: x-coordinate value of point [2]: y-coordinate value of point NOTE: Refer to Section 9:7 for a details on how to use this function without a parameter list. 9:3:3 @POLAR (radius,ray) Like @XY and @NE, the @POLAR function is used to enter a fixed or known coordinate. The coordinate, however, must be entered in a 'polar' format (radius,ray), where: the 'radius' is the distance measured from the intersection of the xy-axis and the 'ray' is the angle measured counter clockwise from the positive x-axis. The 'ray' angle MUST be entered in RADIANS when using this function. While the coordinate is displayed in the same polar format that was used to enter it, it is stored internally as a rectangular coordinate - exactly as all other coordinates. This allows it to be used interchangeably with all other coordinates generated by this package. Return Contents of Array Area: [0]: 1 (number of coordinates pairs) [1]: x-coordinate value of point [2]: y-coordinate value of point NOTE: Refer to Section 9:7 for a details on how to use this function without a parameter list. 9:3:4 @Traverse ([setup],mode,type,angle,distance,fmt) This function computes the next point, given an initial coordinate '[setup]', an 'angle', and a horizontal 'distance'. Any of the four available 'mode' settings (0-3) may be used, however, the angle 'type' is limited to Direction Angles (types 0 thru 4). Refer back to Section 9:2:5 for the 'fmt' display settings that are available. Return Contents of Array Area: [0]: Contains the 'ray' angle in decimal degrees of the line INTO the point computed by this function. This angle (mode=1, type=0) can be used by other functions. [1]: x-coordinate value of point [2]: y-coordinate value of point 9:3:5 @FieldAngle ([backsight],[setup],mode,type,angle,vert,dist,fmt) The @FieldAngle function is similar to the @Traverse function, except that it also allows Field Angles (i.e. 'type' 5 thru 8) to be used to compute the coordinate of the next point. As a result, a '[backsight]' coordinate is required to establish the direction of the line from which the field angle is measured. When using Direction Angles (i.e. 'type' 0 thru 4), the [backsight] coordinate is ignored. This function will except either a slope or a horizontal distance to the next point, depending on how the vertical angle ('vert') parameter is set. For slope distances, the vertical angle is measured 0 upto 90 degrees above the horizontal plane or 360 down to 270 degrees below the horizontal plane. The 'vert' parameter should be set to '0' when entering a horizontal distance. The 'mode' setting applies to both the 'angle' and 'vert' parameters. Return Contents of Array Area: [0]: Contains the 'ray' angle in decimal degrees of the line INTO the point computed by this function. This angle (mode=1, type=0) can be used by other functions. [1]: x-coordinate value [2]: y-coordinate value 9:3:6 @AngAngInt ([point1],[point2],mode,type,angle1,angle2,fmt) Given two coordinate pairs ([point1] and [point2]) and a Direction Angle from each ('angle1' and 'angle2',respectively), this function will compute the point of intersection. Any of the four available 'mode' settings (0-3) may be used, however, the angle 'type' is limited to Direction Angles (i.e. type 0 thru 4). Refer back to Section 9:2:5 for the 'fmt' display settings that are available. Limitations: This function will return an error if you attempt to find the intersection of two parallel lines. Return Contents of Array Area: [0]: 1 (number of coordinates pairs) [1]: x-coordinate value [2]: y-coordinate value 9:3:7 @AngDstInt ([point1],[point2],mode,type,angle1,distance2,fmt) Given two coordinate pairs ([point1] and [point2]) and a Direction Angle measured from the first point (angle1) and a distance measured from the second (distance2), this function will compute the point of intersection. Any of the four available 'mode' settings (0-3) may be used, however, the angle 'type' is limited to Direction Angles (i.e. type 0 thru 4). Refer back to Section 9:2:5 for the 'fmt' display settings that are available. Note: Two solutions are possible. The second is found by adding 180 degrees to the angle measured from [point1]. Return Contents of Array Area: [0]: 1 (number of coordinates pairs) [1]: x-coordinate value [2]: y-coordinate value 9:3:8 @DstDstInt ([point1],[point2],distance1,distance2,fmt) Given two coordinate pairs ([point1] and [point2]) and a distance measured from each (distance1 and distance2), this function will compute the point of intersection. Note: Two solutions are possible. The second solution is found by reversing the order of the coordinate and distance parameters. That is, the computed solution is always clockwise from point 1 to point 2. Limitations: No solution is possible when the distance between points 1 and 2 is greater than the sum of the two distances used in the function. Return Contents of Array Area: [0]: 1 (number of coordinates pairs) [1]: x-coordinate value [2]: y-coordinate value 9:3:9 @CurveRadius ([PC],[PI],mode,radius,fmt) This function returns the coordinate of the PT (Point of Tangency - where the curve ends), given the coordinate of the PC (Point of Curvature - where the curve begins), the PI (Point of Intersection of the two tangents), and the 'radius' of the curve. A curve to the right is assumed if the 'radius' is positive. A negative 'radius' will cause the function to compute the curve to the left. The 'mode' parameter is used to establish the mode of the delta angle that is returned in the array area. Note: Even though this function returns a complete curve solution that is loaded in the cell's array area, its primary results (the PT) can still be referenced by other functions as a single coordinate. The extra information is essentially ignored. Return Contents of the Array Area: [0]: = 3 (number of coordinates pairs) [1] = (x) PT [2] = (y) PT [3] = (x) RP [4] = (y) RP [5] = (x) PI [6] = (y) PI [7] = radius [8] = tangent [9] = chord [10] = bearing of chord (DMS) [11] = arc [12] = delta (central angle) [13] = degree of curve [14] = sector area [15] = segment area 9:3:10 @CurveDelta (PC,RP,mode,delta,fmt) This function returns the coordinate of the PT (Point of Tangency - where the curve ends), given the coordinate of the PC (Point of Curvature - where the curve first begins), the RP (Radius Point), and the 'delta' (central) angle. A curve to the right is assumed if the 'delta' angle is positive; while a negative 'delta' angle entry will cause the function to compute the curve to the left. The 'mode' parameter is used to establish the mode of the delta angle. Note: Even though this function returns a complete curve solution that is loaded in the cell's array space, its primary results (the PT) can still be referenced by other functions as a single coordinate. The extra information is essentially ignored. Return Contents of the Array Area: [0]: = 3 (number of coordinates pairs) [1] = (x) PT [2] = (y) PT [3] = (x) RP [4] = (y) RP [5] = (x) PI [6] = (y) PI [7] = radius [8] = tangent [9] = chord [10] = bearing of chord (DMS) [11] = arc [12] = delta (central angle) [13] = degree of curve [14] = sector area [15] = segment area ------------------------------------------------------------------------ Section 9:4 FUNCTIONS THAT RETURN: MULTIPLE COORDINATES ------------------------------------------------------------------------ The next two functions provide a means of grouping coordinates so that they can be referenced as a single entity. Coordinates can be grouped to form either a line segment or a polygon. For the purposes of this package, a line 'segment' is defined as a series of two (2) or more coordinates that form a contiguous (but not necessarily straight) line. A 'polygon' is comprised of four (4) or more coordinates that form a closed traverse, where the last coordinate closes on the first. The difference between the first and last coordinate is termed the 'error of closure'. 9:4:1 @Segment ([point],[segment],...,[point]) This function creates a special cell that contains a line segment. Segments are used by this spreadsheet as a means of grouping related coordinates so that they can be referenced by a single Cell Address. These cells can be either plotted individually or they can be used as building blocks to form other segments or polygons. When plotted, they are treated as separate entities and take on the color defined for the cell. Note: There are some limitations to the colors that can be used to plot a segment - refer to Section 9:8. The parameter list required by this function may contain as many references to POINT or SEGMENT cells as you like (as long as the final coordinate count is greater than 2). An error will be returned if you attempt to reference any other type of cell. When referencing another SEGMENT cell, you are actually loading all of its coordinates into the array area of the new segment. You can do this in either forward or reverse order. To reverse the order in which a segment's coordinates are loaded, precede the segment's Cell Address with a minus (-) sign. This will cause the last coordinate pair to be loaded first and so on. Once created, cells of this type will display the word "SEGMENT" followed by the total number of coordinate pairs it contains. Return Contents of the Array Area: [0] = number of (x,y) coordinate pairs [1] = x coordinate of 1st point [2] = y coordinate of 1st point [3] = x coordinate of 2nd point [4] = y coordinate of 2nd point : NOTE: Refer to Section 9:7 for a details on how to use this function without a parameter list. 9:4:2 @Polygon ([point],[segment],...[point]) This function creates a special cell that contains a set of coordinates that form a polygon. Once created, the polygon can be either plotted (Section 9:8) or its area and the 'error of closure' can be computed with the @Closure function (Section 9:5:3). Note: Because polygons form closed figures, they technically do not have a beginning and end point. As a result, POLYGON cells can not be used as building blocks to form other polygons or line segments. The parameter list required by this function may contain as many references to POINT or SEGMENT cells as you like. A total of 4 or more coordinates, however, are required to form a polygon. An error will be returned if you attempt to reference any other type of cell. When referencing a SEGMENT cell, you are actually loading its coordinates into the array area of the polygon. You can do this in either forward or reverse order. To reverse the order in which a segment's coordinates are loaded, precede the segment's Cell Address with a minus (-) sign. This will cause the last coordinate to be loaded first. Once the polygon is formed, any difference between the first and last coordinate loaded is termed the 'error-of-closure'. Once created, cells of this type will display the word "POLYGON" followed by the total number of coordinates that make up the polygon. Return Contents of the Array Area: [0] = number of (x,y) coordinate pairs [1] = x coordinate of 1st point [2] = y coordinate of 1st point [3] = x coordinate of 2nd point [4] = y coordinate of 2nd point : NOTE: Refer to Section 9:7 for a details on how to use this function without a parameter list. ------------------------------------------------------------------------ Section 9:5 FUNCTIONS THAT RETURN AN ANGLE and DISTANCE ------------------------------------------------------------------------ The next three functions return the angle and distance between two coordinates. Two display formats are supported by setting the 'fmt' parameter to either 0 or 1, where 0 is the General Display Format and 1 is the Bearing Display Format (refer back to Section 9:2:5). The 'mode' and 'type' parameters are used to define the angle that will be displayed. 9:5:1 @Inverse ([Point1],[Point2],mode,type,fmt) This function returns the angle and distance between two coordinates, [Point1] and [Point2]. The angle that is returned is controlled with the 'mode' and 'type' parameters. Any of the four available 'mode' settings (0-3) may be used, however, the angle 'type' is limited to Direction Angles (i.e. type 0 thru 4). Refer back to Section 9:2:5 for the 'fmt' display settings that are available. Return Contents of the Array Area: [0] = 1 (number of coordinate pairs) [1] = distance [2] = angle 9:5:2 @Radial ([BackSight],[Setup],[ForeSight],mode,type,fmt) This function returns the field angle ('type' 5-8) measured BETWEEN the lines formed by the [Setup] and [Backsight] coordinates AND the [Setup] and [ForeSight] coordinates. In addition to the angle, the distance between the [Setup] and [ForeSight] coordinates is returned. Any of the four available 'mode' settings (0-3) may be used, however, the angle 'type' is limited to Field Angles (i.e. type 5 thru 8). Refer back to Section 9:2:5 for the 'fmt' display settings that are available. Return Contents of the Array Area: [0] = 1 (number of coordinate pairs) [1] = distance [2] = angle 9:5:3 @Closure ([polygon],mode,type,fmt) This function computes the 'Error of Closure' and 'Area' of the coordinates that form a POLYGON (refer to Section 9:4:2). The angle and distance between the first and last coordinates is termed the 'Error of Closure' and is displayed by the function. The 'mode' and 'type' settings control the angle display format. Any of the four available 'mode' settings (0-3) may be used. The angle 'type' setting, however, is limited to Direction Angles (0-4). Refer back to Section 9:2:5 for the 'fmt' display settings that are available. The AREA of the polygon is stored in element position [0], allowing it to be referenced directly by formulas in other cells. Return Contents of the Array Area: [0] = AREA of polygon [1] = DISTANCE between last and first coordinates that form the polygon [2] = ANGLE btw closing coordinates ('ray' angle) [3] = Total length of the sides that form the polygon ------------------------------------------------------------------------ Section 9:6 FUNCTIONS THAT RETURN: MULTIPLE VALUE SOLUTIONS ------------------------------------------------------------------------ 9:6:1 @Triangle (mode,angleA,angleB,angleC,sidea,sideb,sidec) Given any THREE attributes of a triangle, this function will return the full triangle solution in the cell's array area. The following combinations of attributes are supported: ASA, SAA, SSS, or SAS (where "SAS" means Side/Angle/Side). When using this routine, ONLY INCLUDE THREE ATTRIBUTES (even if more are known)! A '0' should be placed in the unknown attribute positions. The results can be displayed using the "=*" Element Display Command described in Chapter 7. NOTE: The SSA option is not supported, since more than one solution is possible. | \ | B \ | \ | \ a c | \ | \ | A C \ |_______________\ b Example (SSS): @Triangle (3,0,0,0,3,4,5) | |__ mode of angles entered and returned Return Contents of the Array Area: [0] = Area [1] = Angle A [2] = Angle B [3] = Angle C [4] = Side a [5] = Side b [6] = Side c ------------------------------------------------------------------------ Section 9:7 NULL POINTS, SEGMENTS, and POLYGONS ------------------------------------------------------------------------ Several of the functions discussed in this chapter can be used with an empty (or null) parameter list. They include: @XY() @NE() @POLAR() @Segment() @Polygon() The 'null' form of these functions allow you to control a cell's Array Area. That is, these functions identify a cell as containing a POINT, SEGMENT, or POLYGON; but they do not create, load, or in anyway modify its Array Area. Those duties are left to you to handle, which can be very handy. Here are a few examples: Example 1 Let's say you create a POLYGON using the standard form of the @Polygon function (i.e. you references several other SEGMENTS and POINTS within its parameter list). And, let's say the coordinates the polygon is made up of are just the ones want - you do not want any of them to change. The problem is, when the referenced POINTS and SEGMENTS change, so will the points used by the POLYGON. That how spreadsheets work. How then do you save a POLYGON? Simple, use the 'null' @Polygon() function. Just enter this function call in the cell that contains the polygon you want to preserve and you will break the ties to other cells on the worksheet. Nothing can effect these coordinates now - except you! Example 2 Let's say you have a line SEGMENT that was formed by referencing a number of POINTS throughout the worksheet and that you want to copy this particular SEGMENT to another worksheet. Will you have a problem doing this? The answer is "yes", if it's not a 'null' SEGMENT. The reason is that copying a SEGMENT (that references other cells) to another worksheet would also require moving the referenced cells! When copied, the SEGMENT will attempt to access cells at the same relative locations on the new worksheet as it did on the original worksheet. If cells do not exist at these locations, an error condition will result. To get around this, simply convert the standard function call to a 'null' @Segment() call on the new worksheet. Note: Copying a POINT, SEGMENT or POLYGON by value (val) will automatically handle this situation for you (see Section 4:2). Example 3 Let's say you have a large polygon that you want to use in a number of different worksheets. And, let's assume this particular polygon is not made up of coordinates that can found on these worksheets. Is there is easy way to load such a polygon. Yes, you can load the polygon into a cell that is defined by the 'null' @Polygon function from a standard text file. Refer to the "f5 Text" option of Section 4:3 for details. REMEMBER! WHEN USING 'NULL' FUNCTIONS, IT IS UP TO YOU TO MAKE SURE THE CELL'S ARRAY AREA EXISTS AND THAT IT IS LOADED CORRECTLY. ------------------------------------------------------------------------ Section 9:8 GRAPHICS ------------------------------------------------------------------------ The @Segment and @Polygon functions are used to define geometric 'shapes' that, among other things, can be plotted. When plotted, lines are drawn between each coordinate that form these entities; while individual coordinates (POINTS) are plotted as single points. Function keys F2 thru F6 of the UTILITY Menu are used to display the points, lines, and polygons created by this Coordinate Geometry Package (see Section 4:7). You can plot as many of these objects as you like (individually or together) by positioning the Cell Pointer over the cell you wish to include and then pressing the F6 (Plot) function key. You can continue to identify cells in this fashion until you press the <Enter> key, which will initiate the display. To provide compatibility with as many PCs as possible, REBEL uses video BIOS calls for all of its graphics displays. While this approach extends graphic capabilities to a wide range of IBM-PCs and compatibles, it does not always take full advantage of your system's video adapter. For example, only three colors can be displayed at a time, which are grouped into two palettes (WHITE-CYAN-MAGENTA and YELLOW-GREEN-RED). The system will attempt to use the color defined for a cell (as long as it remains in the same color palette). ------------------------------------------------------------------------ Section 9:9 EXAMPLES ------------------------------------------------------------------------ _______________________ IMPORTANT Before trying out any of the following examples, be sure that the Column Width of column 3 is set to 27 (refer to 'Width' option discussed in Section 4:6). And, initialize cells [1,3], [2,3], and [3,3] with the following coordinate pairs, by: ...moving the Cell Pointer to [1,3] and enter: @XY(2000,1000) ...moving the Cell Pointer to [2,3] and enter: @XY(500,500) ...moving the Cell Pointer to [3,3] and enter: @XY(1000,1000) IMPORTANT _______________________ Example 1) TRAVERSE North 45 degrees 15 minutes and 30 seconds East a distance of 378 feet from the point loaded in cell [3,3]. With the Cell Pointer positioned over cell [5,3] enter: @Traverse([3,3],3,1,145.1530,378,0) | | | |_ XY display | | | format (9:2:5) | | | mode: degrees (dms) _| | |__ bearing angle (9:2:4) (9:2:2) |__ angle type: bearing (9:2:3) Answer Displayed: (1268.49, 1266.08) Example 2) Assume that, in the field, you are setup over point [3,3] and that you are backsighting point [2,3]. Compute the coordinate of a new point by turning an angle RIGHT of 22 degrees 30 minutes and measuring a distance of 78 feet. With the Cell Pointer positioned over cell [6,3] enter: mode: degrees (dms) __ | @FieldAngle([2,3],[3,3],3,5,22.30,0,78,0) | | field angle right __| |_ vertical angle Answer: (927.94, 970.15) Example 3) Compute the coordinate of the point that measures 750 feet from point [2,3] and 550 feet from the point [3,3]. With the Cell Pointer positioned over cell [7,3] enter: @DstDstInt([2,3],[3,3],750,550,0) Answer: (510.07, 1249.93) Example 4) Assume that point [2,3] is the PC (point of curve) and that point [1,3] is the PI (point of intersection) of a curve that bears to the left with a radius of 1028 feet. Compute the coordinate of the PT (point of tangency); together with a FULL CURVE SOLUTION. With the Cell Pointer positioned over cell [8,3] enter: mode to be used to return delta angle _ | @CurveRadius([2,3],[1,3],3,-1028,0) | radius must be negative to compute __| a curve to the left Answer: (934.36, 2198.08) NOTE: Use the "=*" command at the Input Line to review the curve solution loaded in the cell's array area. Example 5) Form a POLYGON with the initialized coordinates. With the Cell Pointer positioned over cell [9,3] enter: @Polygon([1,3],[2,3],[3,3],[1,3]) NOTE: This figure can now be plotted (see Section 9:8). Example 6) Compute the ERROR-OF-CLOSURE of the polygon formed in the above example. With the Cell Pointer positioned over cell [10,3] enter: @Closure([9,3],3,1,1) Answer: (N90 00'00.0"E 0.00) NOTE: The AREA of the polygon is loaded in element position [0]. To view it, type "=0" at the Input Line (without the quotes). Example 7) Compute the ANGLE and DISTANCE between the coordinates loaded in cells [2,3] and [1,3]. With the Cell Pointer positioned over cell [11,3] enter: @Inverse([2,3],[1,3],3,1,1) | bearing/distance display format _| Answer: (N71 33'54.1"E 1581.14) Example 8) Compute the ANGLE formed by the intersection of two lines formed by the coordinates loaded in cell [1,3] (the backsight), cell [2,3] (the setup or intersection), and cell [3,3] (the foresight). With the Cell Pointer positioned over cell [12,4] enter: return angle right __ | @Radial([1,3],[2,3],[3,3],3,5,0) | general display format__| Answer: (RHT(dms) 26.33542, 1581.14) _____________________________ ________________________________________/ Chapter 10 MATRIX Chapter Contents Section 10:1 ABOUT THE MATRIX FUNCTIONS Section 10:2 LOADING AND EDITING A MATRIX Section 10:3 DISPLAYING MATRIX ELEMENTS Section 10:4 REFERENCING OTHER MATRICES Section 10:5 MATRIX FUNCTIONS THAT RETURN ANOTHER MATRIX 10:5:1 @Matrix . . . . . . . creates and loads a matrix 10:5:2 @MatrixAdd . . . . . . adds matrix A & B 10:5:3 @MatrixCrossProduct. . cross product of matrix A & B 10:5:4 @MatrixExponent . . . multiplies a matrix by itself 10:5:5 @MatrixInverse . . . . inverts a matrix 10:5:6 @MatrixProduct . . . . multiples matrix A & B 10:5:7 @MatrixScalar . . . . scalar multiplication 10:5:8 @MatrixSolution . . . returns solution set 10:5:9 @MatrixSubtract . . . subtracts matrix B from A 10:5:10 @MatrixTranspose . . . transposes matrix A Section 10:6 MATRIX FUNCTIONS THAT RETURN A SINGLE VALUE 10:6:1 determinant . . . . . returns the determinant 10:6:2 dotproduct . . . . . . returns the dot product Section 10:7 USING 'NULL' MATRICES Section 10:8 USING MATRICES WITH POINTS, SEGMEMTS, AND POLYGONS Section 10:9 EXAMPLES ------------------------------------------------------------------------ Section 10:1 ABOUT THE MATRIX FUNCTIONS ------------------------------------------------------------------------ Like the functions used in the Coordinate Geometry package, these functions also take advantage of several of REBEL's more advanced features. As a result, it is NOT advisable to jump immediately into this chapter without first becoming familiar with the topics discussed in Chapter 6. I highly recommend, that at a very minimum, you become familiar with the concept of "Cell Array Areas" (Section 6:5) before continuing. Once you learn how a matrix is created and stored, you will find that these functions are simple to use. As with the COGO functions, there are a few important differences that distinguish them from the standard functions that were covered in Charter 8. The most significant of these differences is the way in which values are returned and displayed. While standard functions (and expressions) always return a single value that is displayed by the cell, MATRIX @functions always return multiple values that are loaded into the cell's Array Area. These values comprise the elements of the matrix and are not displayed by the spreadsheet. Cells that contain matrices will display the the number of rows and columns a matrix contains (its size) followed by the word "MATRIX". Another important distinction is that MATRIX @functions can not be nested within the parameter lists of other functions, since they return multiple values. If used, they must be the only entry in a cell's formula. ------------------------------------------------------------------------ Section 10:2 DEFINING, LOADING AND EDITING A MATRIX ------------------------------------------------------------------------ The '@Matrix' function (described in Section 10:5:1) is used to create and load a matrix whose element values are known. All of the other matrix functions described in this chapter create new matrices based on an operation performed on one or more existing matrix. You can load a matrix by either entering the values that form the matrix directly into the parameter list of the '@Matrix' function: @Matrix (3,4, 2,4,7,0, 1,1,9,7, 3,1,0,9) or by setting up the @Matrix function to reference values in other cells that can be more easily changed: @Matrix (2,3, [5,1],[5,2],[5,3], [6,1],[6,2],[6,3]) One word of caution, if you decide to use the second method, keep in mind that each time a value in cells [5,1] thru [6,3] is changed, it could trigger a whole series of matrix operations to be performed. Since you'll probably want to prevent this from happening until you have completed all of your changes, you will need to set the +NoCalc flag described in Section 4:1. This setting will prevent the cell that contains the matrix (whose element values your are modifying) from triggering other cells from recalculating - even though their values are affected by the changes. The 'CelCalc' option listed under the UTILITY menu (Section 4:7) can then be used to force a recalculation of the affected cells whenever you are ready. ------------------------------------------------------------------------ Section 10:3 DISPLAYING A MATRIX ------------------------------------------------------------------------ As mentioned earlier, the spreadsheet does not display the element values of a matrix. It is left to the you to decide whether or not to display these values and by which method. The simplest way to display the elements of a matrix is to use the "=" Shortcut Command. To use this command, simply move the Cell Pointer to the cell that contains a matrix and type an equal sign (=) at the Input Line and then press the <Enter> key. This will cause a single row of the matrix to be displayed each time the <Enter> key is pressed. There are a number of variations to this command that can also be used (refer to Chapter 7 for details). You can also display the elements of a matrix, on the screen, by referencing the Array Area of the matrix cell from other cells. To do this, however, it requires a knowledge of how the matrix is stored in the Array Area. The first two array positions contain the number of ROES and COLUMNS in the matrix. This is followed by the value of each element beginning with the first column of the first row and moving left-to-right to the last column in the last row. The first two parameters determine the total number of entries that are contained in the array. Array Position Contents [1] The number of ROWS in the matrix [2] The number of COLUMNS in the matrix [3] Element loaded in the 1st COLUMN of the 1st ROW [4] Element loaded in the 2nd COLUMN of the 1st ROW : [n] Element in the last COLUMN of the last ROW For example, if you wanted to display the 1st element in the 1st row of a matrix that is defined is cell [5,1], you could make the following array reference from any empty cell: [5,1,3]. REMEMBER! THE FIRST TWO ARRAY POSITIONS CONTAIN THE NUMBER OF ROWS AND COLUMNS IN THE MATRIX. THE ELEMENT VALUES ARE LOADED, STARTING AT ARRAY POSITION THREE (3)! ------------------------------------------------------------------------ Section 10:5 MATRIX FUNCTIONS THAT RETURN ANOTHER MATRIX ------------------------------------------------------------------------ All of the functions described in this section will return a 'matrix' upon completion. As a result, the 'Cell Address' of any CELL that contains one of these function calls can be used as a parameter within another matrix function call. Remember, a Matrix @Function 'call' can not be nested within the parameter list of another function call, but the 'Cell Address' of a CELL that contains a matrix CAN be used as a parameter. So, whenever you see a function parameter surrounded by square brackets, such as [matrix_A], it is referring to the the Cell Address of a cell that contain a matrix (or in some cases a POINT, SEGMENT or POLYGON). If an error is detected by any of these functions, a series of question marks (?????) will appear in the cell's display. You can identify the exact problem by moving the Cell Pointer to the cell in question and then entering the Edit Mode. This will cause an error message to be displayed that will hopefully help you correct the problem (refer back to Chapter 5). ________________________________________________________ 10:5:1 @Matrix (rows, columns, element1, element2, ... elementn) This function creates a matrix of the size defined by the first two parameters. The next series of parameters are used to load the matrix elements, beginning with the first column of the first row and moving left-to-right to the last column in the last row. The first two parameters determine the total number of entries that must be made. For example, the following function call would be used to load this 3x4 matrix: | 2 4 7 0 | | 1 1 9 7 | | 3 1 0 9 | @Matrix (3,4, 2,4,7,0, 1,1,9,7, 3,1,0,9) Return contents of the cell's Array Area: [0] (not used) [1] The number of ROWS in the matrix [2] The number of COLUMNS in the matrix [3] Element loaded in the 1st COLUMN of the 1st ROW [4] Element loaded in the 2nd COLUMN of the 1st ROW : [n] Element in the last COLUMN of the last ROW ________________________________________________________ 10:5:2 @MatrixAdd ([matrix_A],[matrix_B]) ADDS matrix A and matrix B, returning matrix C that is loaded in the Array Area of the cell that contains this function call. The format used to load matrix C in the cell's Array Area is described in Section 10:5:1. Note, the parameter '[matrix_A]' refers to the Cell Address of the cell that contains matrix A. Requirements: (i) Matrix A and B must be the same size. ________________________________________________________ 10:5:3 @MatrixCrossProduct ([matrix_A], [matrix_B]) Computes the CROSS PRODUCT of matrix A and B, returning matrix C that is loaded in the Array Area of the cell that contains this function call. The format used to load matrix C in the cell's Array Ares is described in Section 10:5:1. Note, the parameter '[matrix_A]' refers to the Cell Address of the cell that contains matrix A. Requirements: (i) Both matrix A and B must be 1X3 matrices. ________________________________________________________ 10:5:4 @MatrixExponent ([matrix_A], power) Matrix A is MULTIPLIED by itself the number of times set by the 'power' variable, returning matrix C that is loaded in the Array Area of the cell that contains this function call. The format used to load matrix C in the cell's Array Ares is described in Section 10:5:1. Note, the parameter '[matrix_A]' refers to the Cell Address of the cell that contains matrix A. Requirements: (i) Matrix A must be a square matrix. (ii) The 'power' variable must be a whole number > 1 ________________________________________________________ 10:5:5 @MatrixInverse ([matrix_A]) Computes the INVERSE of matrix A, returning a matrix (C) that is loaded in the Array Area of the cell that contains this function call. (It should be noted that multiplying matrix A by matrix C will produces the 'identity matrix'.) The format used to load matrix C in the cell's Array Area is described in Section 10:5:1. Note, the parameter '[matrix_A]' refers to the Cell Address of the cell that contains matrix A. Requirements: (i) Matrix A must be a square matrix. ________________________________________________________ 10:5:6 @MatrixProduct ([matrix_A], [matrix_B]) MULTIPLIES matrix A by matrix B, returning matrix C that is loaded in the Array Area of the cell that contains this function call. Matrix C will have the same number of ROWS as matrix A and the same number of COLUMNS as matrix B. The format used to load matrix C in the cell's Array Area is described in Section 10:5:1. Note, the parameter '[matrix_A]' refers to the Cell Address of the cell that contains matrix A. Requirements: (i) The number of columns in matrix A must equal the number of rows in matrix B. ________________________________________________________ 10:5:7 @MatrixScalar ([matrix_A], scalar) MULTIPLIES matrix A by a single number (scalar), returning matrix C that is loaded in the Array Area of the cell that contains this function call. The format used to load matrix C in the cell's Array Ares is described in Section 10:5:1. Note, the parameter '[matrix_A]' refers to the Cell Address of the cell that contains matrix A. ________________________________________________________ 10:5:8 @MatrixSolution ([matrix_A]) SOLVES a system of simultaneous equations represented by the augmented matrix A, returning the solution set in matrix C that is loaded in the Array Area of the cell that contains this function. Matrix C is always returned as a (m x 1) matrix, where m equals the number of rows in matrix A. The format used to load matrix C in the cell's Array Ares is described in Section 10:5:1. Note, the parameter '[matrix_A]' refers to the Cell Address of the cell that contains matrix A. Requirements: (i) Matrix A must be an augmented matrix, where the rows of the matrix represent the coefficients of the variables followed by the constant term. For example: System Augmented Matrix x - y + z = 0 | 1 -1 1 0 | 3x + 4y + 2z = 1 | 3 4 2 1 | 2x + 7y + 13z = 2 | 2 7 13 2 | (ii) The number of equations (rows) must equal the number of variables. That is, matrix A must have one more column than it does rows. ________________________________________________________ 10:5:9 @MatrixSubtract ([matrix_A],[matrix_B]) SUBTRACTS matrix B from matrix A, returning matrix C that is loaded in the Array Area of the cell that contains this function call. The format used to load matrix C in the cell's Array Area is described in Section 10:5:1. Note, the parameter '[matrix_A]' refers to the Cell Address of the cell that contains matrix A. Requirements: (i) Matrix A and B must be the same size. ________________________________________________________ 10:5:10 @MatrixTranspose ([matrix_A]) TRANSPOSES matrix A (i.e. flips the matrix so that the rows become the columns and the columns become the rows), returning matrix C that is loaded in the Array Area of the cell that contains this function call. The format used to load matrix C in the cell's Array Ares is described in Section 10:5:1. Note, the parameter '[matrix_A]' refers to the Cell Address of the cell that contains matrix A. ------------------------------------------------------------------------ Section 10:6 MATRIX FUNCTIONS THAT RETURN A SINGLE VALUE ------------------------------------------------------------------------ The following two functions return single values that are displayed by by the spreadsheet. As a result, both functions can be nested within other expressions. ________________________________________________________ 10:6:1 determinant ([matrix_A]) Returns the DETERMINANT of matrix A. Note, the parameter '[matrix_A]' refers to the Cell Address of the cell that contains matrix A. Requirements: (i) Matrix A must be a square matrix. ________________________________________________________ 10:6:2 dotproduct ([matrix_A],[matrix_B]) Returns the DOT PRODUCT of matrix A and B (defined as the sum of the products of the corresponding elements). Note, the parameter '[matrix_A]' refers to the Cell Address of the cell that contains matrix A. Requirements: (i) The number of rows and the number of columns in matrix 'A' and matrix 'B' must be equal. (ii) Either the number of rows or columns must equal 1, but not both! ------------------------------------------------------------------------ Section 10:7 USING 'NULL' MATRICES ------------------------------------------------------------------------ A special variation of the '@Matrix' function allows it to be used with an empty (or 'null') parameter list. @Matrix() The important thing to remember about a NULL MATRIX, is that it is totally user defined. That is, it is left to you to create and load the cell's Array Area with the correct element values. The @Matrix() function will neither create or alter the Array Area of a new or existing cell. It simply identifies a cell as containing a matrix. This fact can be taken advantage of when it's necessary to save the results of a matrix you have computed. Since the Array Area of an existing matrix is already setup correctly, all that is necessary to do to preserve its contents is to enter "@Matrix()" at the Input Line (with the Cell Pointer positioned over the matrix cell). This will form a NULL MATRIX, which removes the matrix operation that was used to compute the original matrix. The element values are unaffected. A NULL MATRIX is also useful when it comes to loading a large matrix from an external text file (refer to loading 'Text' files in Section 4:3). REMEMBER, WHEN USING A NULL MATRIX, IT IS UP TO YOU TO MAKE SURE THAT AN ARRAY AREA FOR THE CELL EXISTS AND THAT IT IS LOADED PROPERLY. The following diagram defines how the Array Area of a matrix must be setup. Notice that the first two entries always contain the number of rows and columns in the matrix. These values are followed by the element values of the first row, the second row, and so on. [1]: number of ROWS in the matrix [2]: number of COLUMNS in the matrix [3]: first column entry of the first row [4]: second column entry of the first row : [n]: last column entry of the last row ------------------------------------------------------------------------ Section 10:8 USING MATRICES WITH POINTS, SEGMEMTS, AND POLYGONS ------------------------------------------------------------------------ One of the most powerful features of the this package is its ability to perform repeated @MatrixProduct operations on the coordinates of POINTS, SEGMENTS, and POLYGONS, forming a new cell (of the same type) that contains the transformed coordinates. To do this, the spreadsheet views each coordinate pair as an independent 1X3 matrix (the third element is always set to 1) that can be multiplied by any 3x3 transformation matrix. When doing this, the only rule that must be followed is that the POINT, SEGMENT, or POLYGON cell must be the A matrix (the first reference) in the function's parameter list. As an example, to rotate, scale, or translate the coordinates of a polygon, the function call should be setup as follows: @MatrixProduct ([polygon], [transformation_matrix]) When the above operation is performed, a new cell will be created based on the cell type of the first parameter (in this case a POLYGON) with each of its coordinates multiplied times the transformation matrix. ------------------------------------------------------------------------ Section 10:9 EXAMPLES ------------------------------------------------------------------------ Example 1) MULTIPLY MATRIX 'A' BY MATRIX 'B' A = | 2 1 0 | B = | 1 -1 2 | | 0 1 1 | | 1 2 3 | | 0 1 1 | Load matrix A into cell [3,2] by entering the the following formula: @Matrix (2,3, 2,1,0, 0,1,1) Load matrix B into cell [3,4]: @Matrix (3,3, 1,-1,2, 1,2,3, 0,1,1) To multiply matrix A by B, enter the following function in cell [3,6]: @MatrixProduct ([3,2],[3,4]) To quickly display the elements of the new matrix (C), position the Cell Pointer over cell [3,6] and type a lone equal sign (=) at the Input Line followed by the <Enter> key. This will cause the first row of the new matrix to be displayed at the Input Line. Press the <Enter> key again to display the next row, and so on. Example 2) DISPLAY THE ELEMENTS OF MATRIX 'C' ON THE SCREEN. Locate an area on your worksheet large enough to display the matrix. In this case, we'll use the range of cells between [5,3] and [6,5]. To display matrix C (loaded in cell [3,6]), position the Cell Pointer over cell... [5,3] and enter: [3,6,3] [5,4] and enter: [3,6,4] [5,5] and enter: [3,6,5] [6,3] and enter: [3,6,6] [6,4] and enter: [3,6,7] [6,5] and enter: [3,6,8] IMPORTANT! Notice that the first element of matrix C is stored in array position "3" ([3,6,3] - not position one. The first two array positions of all cells that contain matrices are reserved for the number of 'rows' and 'columns' in the matrix (refer back to Section 10:3). IMPORTANT! The one problem with displaying a matrix as just described is that cells [5,3] thru [6,5] will not update themselves when the element values of matrix C (cell [3,6]) are changed. Refer to Section 6:5 for a detailed explanation as to why this is the case. To force these cells to update, you can set the +ReCalc flag for each of these cells (described in Section 4:1). This will cause cells [5,3] thru [6,5] to automatically update their values - each time ANY change is made to the worksheet. Example 3) MODIFY MATRIX 'A' SO THAT 2 OF ITS ELEMENT VALUES CAN BE SET FROM VALUES ENTERED IN OTHER CELLS. First, enter a value in cell [1,2] and cell [2,2] (any value). Then modify the formula in cell [3,2] (matrix A) to read: @Matrix (3,3,[1,2],1,3,[2,2],5,6) Now, each time you reset the values in cells [1,2] and [2,2]: (i) the value of matrix A will change (ii) matrix A and B will be re-multiplied, updating the value of matrix C in cell [3,6] (iii) and, the values displayed in cells [5,3] thru [6,5] will be updated (provided you set the +ReCalc flag for these cell). ____________________________ _________________________________________/ Chapter 11 SCRIPTS INTRODUCTION Scripts are one of the most powerful features offered by REBEL. They extend the power and capabilities of the spreadsheet by allowing you to tailor functions, utilities, and even full applications to fit your specific needs. They are also easy to use. Simply reference the library that contains the script you want and REBEL's powerful Script Manager will handle the rest for you. REBEL comes with a standard a script library called STDLIB.REB. While it contains many useful functions, it's far from complete in terms of what is possible. The documentation for this library is included in a file called STDLIB.DOC. _________________________________________ Chapter Contents Section 11:1 What are scripts? Section 11:2 Using scripts Section 11:3 The Script Index Section 11:4 The "Swap Area" Section 11:5 Trouble Shooting Section 11:6 Writing your own scripts _________________________________________ ----------------------------------------------------------------------- Section 11:1 WHAT ARE SCRIPTS? ----------------------------------------------------------------------- Scripts are functions or procedures that contain one or more statements that can be organized and executed in a highly structured manner to perform a specific task. The structure or syntax used to write scripts is very similar to the 'C' programming language. Scripts have several advantages over cell formulas. They allow you to control the number and the order in which statements are executed before returning control to the spreadsheet. They can also be used as building blocks to access other scripts and/or a whole set of built-in 'Toolkit' functions that provide a complete interface to every aspect of the spreadsheet. And finally, scripts combine the powerful computational features of a spreadsheet with the flexibility of a traditional programming language. In general, scripts are grouped into three major categories: functions, utilities, and applications. FUNCTIONS: While REBEL provides 94 built-in functions that perform a wide range of spreadsheet computations, there are literally thousands of other functions (geared to specific industries or occupations) that could be developed. Fortunately, REBEL makes it is easy to add libraries of such routines with a highly structured, C-like, script language. These functions (or scripts) look and behave just like built-in functions and are also automatically included in REBEL's recalculation engine. UTILITIES: Scripts can also be designed to perform specialized tasks that fall under the same category as most 'macros' (i.e. utilities). Scripts of this type usually perform some action or command currently not available from the Function Key Menus. As an added option, these scripts can also be assigned and executed from a special set of function keys (see Section 4:5). APPLICATIONS: Some scripts become so powerful that they actually take over control of the spreadsheet itself. These scripts often use the spreadsheet only as a powerful computational tool by hiding the spreadsheet matrix and redesigning the user interface. This makes the application easier to learn and less intimidating for the user. ----------------------------------------------------------------------- Section 11:2 USING SCRIPTS ----------------------------------------------------------------------- Before a script can be accessed from a library, the name of the library file must either be 'attached' in REBEL's start up sequence with the -L: option or attached from within the spreadsheet itself with the 'Attach' Function Key Command discussed in Section 4:5. Once a library has been referenced in this fashion, REBEL's Script Manager handles the rest for you. For example, to access the scripts in REBEL's standard library, STDLIB.REB, simply restart the program using the -L: option (followed immediately by the name of the library): REBEL -L:STDLIB It's that simple! ----------------------------------------------------------------------- Section 11:3 THE SCRIPT INDEX ----------------------------------------------------------------------- While there is no limit to the number of libraries that can be attached with the -L: option or with the 'Attach' command, REBEL's Script Manager will only keep track of the first 64 scripts it encounters. It does this by loading information about each script into a special 'Script Index' as each library file is scanned. The information contained in this index is then used by the Script Manager to manage and execute scripts in a special memory area called the Swap Area (discussed in more detail in the next section). The important thing to remember is that ONLY the scripts found in the Script Index can be accessed by the Script Manager and that scripts ARE NOT loaded into the Swap Area until they are actually used in the spreadsheet. The Script Index contains a great deal of useful information designed to help you to fine tune your system. You can inspect its contents with the 'Index' Function Key Command explained in Section 4:5. This command displays the following information about each script: - The SIZE of the script in bytes (characters) - Address of the script within the Swap Area - The NAME of the library that contains the script - The library's version number (set by its author) - The version of the Script Library Builder used to create the library. - The recommend 'minimum' version of REBEL to use NOTE: From time to time, it may become necessary to clear the Script Index to make room for scripts found in other libraries. This can be done with the 'Detach' function key command described in Section 4:5 of this manual. ----------------------------------------------------------------------- Section 11:4 THE SWAP AREA ----------------------------------------------------------------------- Before a script can be executed, the Script Manager must copy the body of the script from a library file to a special memory area called the Swap Area. Once loaded, scripts will either remain in this area (where they can be quickly re-executed) or will be temporarily swapped out to make room for other scripts that require additional memory. In either case, the Script Manager keeps track of what's going on. If the Script Manager cannot load a script due to insufficient memory, it will begin swapping out scripts currently not in use. If enough memory still cannot be collected after completing this process, the Script Manager will inform you that the memory in the Swap Area has been exhausted. You can correct the problem by increasing the size of the Swap Area (see NOTE below). There are two ways to do this. First, you can reset the size of the Swap Area by restarting the program using the -M: option (followed immediately by the size of the Swap Area - in bytes). For example, a start up sequence that would attach the scripts contained in library STDLIB.REB and then set the size of the Swap Area to 2000 bytes would look something like this: REBEL -L:STDLIB -M:2000 The 'SwpArea' function key command (see Section 4:5) can also be used to adjust the size of the Swap Area. This command has the added advantage of allowing you to set the size of the Swap Area without exiting the program. While setting the Swap Area to the smallest possible size conserves memory which can be used for other purposes, it can also slow the overall performance of recalculations by forcing the system to continuously swap the same scripts in and out of memory as they are used. In general, the larger the Swap Area the faster system will run. You may want to experiment a little to find the best possible trade off between memory and performance before deciding on the optimum size of the Swap Area. NOTE: You can determine the minimum number of bytes required to run each script by using the 'Index' Function Key command to inspect the Script Index. (The current size of the Swap Area can be displayed by executing the 'SwpArea' Function Key command and then pressing the <Enter> key without an entry.) ----------------------------------------------------------------------- Section 11:5 TROUBLE SHOOTING ----------------------------------------------------------------------- When using a script within a cell formula, several problems can occur that could cause a series of question marks (??????) to be displayed by the cell. The fastest way to determine what may have gone wrong is to move the Cell Pointer to the cell containing the problem and then to enter the Edit Mode (see Chapter 5). This will cause an error message to be displayed that will hopefully help you solve the problem. If the error message does not provide you with enough information and you are sure the problem is being caused by a script, review the following list of problem scenarios for a possible solution. ERROR: "script Swap Area is exhausted" Problem: The Swap Area may be exhausted, even though it is larger than the biggest script listed in the Script Index. This can happen when one script calls another script. In case, both scripts are "in use"; and, therefore, can not be swapped out. Solution 1: Use the 'SwpArea' function key command (see Section 4:5) to increase the size of the Swap Area, a little at a time, until the script is able to run. ERROR: "undefined script or function" Problem: The spreadsheet is unable to locate the script. Solution 1: The library containing the script you are attempting to access may not have been properly 'attached'. Try locating the script in the Script Index. If you can not find it, try re-attaching the library with the 'Attach' Function Key command (refer to Section 4:5). Solution 2: The name of the script may not be spelled correctly. Remember script names are case sensitive (i.e. "sum" and "Sum" are not the same). Solution 3: You may have 'attached' a series of libraries that (in combination) total more than 64 scripts. As a result the script you are attempting to access may not have been loaded in the index. Try clearing the Script Index with the 'Detach' function key command (Section 4:5) and re-attaching the library containing the script that you need - first. ERROR: "out of memory" Problem: Scripts containing locally declared variables dynamically reserve and then free up a portion of memory for their own use. This memory is not part of the Swap Area. If a worksheet happens to consume a large amount of memory, it is possible that these scripts will be unable to allocate the memory it needs to run. Solution 1: Try reducing the size of your worksheet. Hopefully, this will free up enough memory that the script will be able to run. ERROR: "syntax error" Problem: As time goes on and new features are added to REBEL, it will be possible to write scripts that will not run properly on an older version of REBEL. Solution 1: Use the 'Index' command to verify that you are use a version of REBEL that is capable of running the script in question. There is no charge to upgrade this program, if it becomes necessary. ----------------------------------------------------------------------- Section 11:6 WRITING YOUR OWN SCRIPTS ----------------------------------------------------------------------- All versions of REBEL 3.0 and higher come with a built-in Script Manager that can access and execute scripts found in any (.REB) library file. Writing and creating these libraries, however, is an optional feature requiring a license for REBEL's Script Library Builder. The cost of this additional software is $29.00 (see Order Form at the end of this manual). Because the subjects related to writing scripts are covered extensively in the documentation that accompanies the Script Library Builder, only a brief discussion of these topics is included here. The following section, therefore, is only intended to be an introduction and is by no means complete. _________________________________ One of the first things you'll notice about REBEL's Script Language (RSL) is that it's a far cry from the 'macro' languages supported by most spreadsheets. RSL is a highly structured, C-like programming language that is both powerful and easy to learn. And, if you are an experienced 'C' programmer, you'll find that writing these scripts is almost second nature. In fact, the body of many scripts will actually compile in 'C'. (If you're not familiar with 'C', you can take comfort in the fact that you will also be learning one of the most popular programming languages ever developed as you learn to write scripts.) Before going into detail on the specific components that make up a script, it might be useful to see what an actual script looks like. For this, let's use the famous "Hello world!" example: hello (val: row, col) /* pass in two values - the cursor pos */ chr: buffer[80]; /* declare a local string of 80 chars */ { cursor(row,col); /* position cursor on screen */ strcpy(buffer,"Hello world!"); /* save string constant */ fputs(buffer,0); /* write 'string' to screen */ } The above example is slightly more complicated than it needs to be to demonstrate local variable declarations and the parameter passing features of scripts . Notice that the first line of the script contains the name of the script followed by the names of two arguments that are passed to it from another script or from the worksheet. These arguments must be enclosed within a set of parentheses and can include four distinct data types: values (val:), arrays (val: []), character strings (chr:), and cell address pointers (cel:). Local parameters can also be declared that are only visible while the script is being executed. If used, these variables MUST precede the opening curly brace '{' that begins the body of the script. This differs from 'C', where the local parameters follow the opening brace. There are 3 data types that can be declared locally: (1) values, (2) arrays, and (3) character strings. The body contains the statements that are executed when the script is run. It follows the local parameter declarations and is enclosed within a set of curly braces {}. RSL supports 3 programming constructs (if, for, while) that can be used to control the order in which statements are executed. The 'if-else' construct: if (condition) { statement(s); } else if (condition) { statement(s); } else { statement(s); } The 'for' construct: for (initialize; condition; increment) { statement(s); } For example: for (ii=0; ii<80; ii+=1) { /* blank out 'name' */ name[ii] = ' '; } The 'while' construct: while (condition) { statement(s); } RSL also supports: comments (/* .. */) 'break' 'continue' 'return' TOOLKIT FUNCTIONS In addition to its powerful programming constructs, RSL supports an extensive set of over 30 Toolkit functions. These routines are accessible only to scripts and cannot be used within cell formulas. They extend a wide range of capabilities that include access to the primary elements that control the spreadsheet, as well as to I/O and screen handling routines. NOTE: A more detailed description of each of these functions is included with the documentation accompaniing REBEL's Script Library Builder. fopen - Opens a text file for read, write, or append access. fclose - Closes a file opened by 'fopen'. fgets - Reads the first 'n' characters from a text file (or until an End-of-Line marker is encountered), placing the characters read into the 'string' parameter. This routine can also read characters from the keyboard by setting the 'fp' parameter to zero (0). fputs - Writes the character in the 'string' parameter to a text file that has been previously opened by 'fopen'. This function can also be used to write characters to the screen by setting the 'fp' parameter to zero (0). sprintf - Converts and formats an argument list made up of any number or type of parameters into a character string. Supports '%s' and '%f' conversions. getchar - Returns the ASCII value of the next character returned from the keyboard. cursor - Moves the screen cursor to a specific row and column position. erase - Erases 'n' character on the screen. clear - Clears the screen. refresh - Re-displays the spreadsheet. drawbox - Draws a box of the screen. savework - Saves a worksheet in REBEL's native .RB2 format. loadwork - Loads a worksheet that has been saved in REBEL's native .RB2 format. report - Copies a portion of a worksheet to a disk file. freework - Deletes all data at the specified worksheet level. makentry - Makes a cell entry at a specific location. getentry - Returns the cell entry (formula) of a cell. contents - Returns a copy of the text that is displayed by a cell. recalc - Recomputes a cell's formula. purgecell - Purges (deletes) a cell from the worksheet. copycell - Copies a cell to another location. movecell - Moves a cell to another location on the spreadsheet. nextcol - Returns the column that contains the next occupied cell. nextrow - Returns the next row that contains data. lastrow - Returns the last row at a level that contains data. gocell - Moves the Cell Pointer to any location on the worksheet. cprow - Returns the row number of the Cell Pointer's current position. cpcol - Returns the column number of the Cell Pointer's current position. cplvl - Returns the level number of the Cell Pointer's current position. cprng - Allows the Cell Pointer to be moved from within a script. cwidth - Returns (or sets) the width of a specific column. attribute - Returns an attribute setting of a cell, given the attribute's id number. In all, over 20 attribute settings can be viewed or modified. seterrno - Clears or sets the 'errno' flag value. errno - Returns the value of the 'errno' flag. errlist - Returns a system error message. _____________________________ ________________________________________/ Appendix A SUPPORT If you have a question regarding this manual or feel the program has a bug, don't hesitate to contact me. Currently, the only support I'm able to offer is by mail. I will, however, make every effort to respond to your questions as soon as possible. When you write, please include the following: General information about your computer: - Your current version of REBEL (version 2.1a, 3.0, etc.) - The version of DOS you are using (3.1, 5.0, etc..) - The type of computer you're using (8088, 80286, 80386, etc..) - Available RAM (512K, 640K, etc..) - Type of Graphic Card (CGA, EGA, VGA, Hercules, etc..) - Any other information you feel may be pertinent Specific information about your problem: - A SIMPLE example that isolates the problem - Any error messages displayed when entering the Edit Mode - The exact steps required to reproduce the problem. - I can't return it, but a floppy disk that contains the problem would be very useful. Send to: Brad L. Smith REBEL Software P.O. Box 270277 Fort Collins, CO 80527 _____________________________ ________________________________________/ Appendix B LICENSE LICENSE AGREEMENT You are free to use, copy, or distribute the DOS (real mode) version REBEL 3.0 and higher, provided it meets the terms of this agreement. This software is copyrighted and should be treated accordingly. It is not in the public domain and the author retains full ownership. Your right to use REBEL automatically terminate if you fail to comply with any provision of this License Agreement or upon written notification from the author. Permission is granted to copy and distribute UNMODIFIED copies of this software - provided that no payment, other than for the transfer medium, is received. The minimum package of files distributed must include the following files: REBEL.EXE, CHAP1-4.DOC, CHAP5-11.DOC, STDLIB.REB, and STDLIB.DOC. Permission is also granted to include this software with other products or services provided REBEL's trademark is be clearly identified and the minimum package of files (described above) is included. IMPORTANT! REBEL's Script Library Builder (BUILD.EXE) is not part of this product! Under no circumstances may this program be copied or distributed. WARRANTY DISCLAIMER THIS SOFTWARE AND MANUAL IS LICENSED "AS IS" AND WITHOUT ANY EXPRESSED OR IMPLIED WARRANTIES WHATSOEVER. THE USER MUST ASSUME THE ENTIRE RISK: 1) OF USING THIS PRODUCT; 2) OF ANY DAMAGES RESULTING FROM ITS USE; 3) FOR ITS FITNESS FOR ANY PARTICULAR PURPOSE. UNDER NO CIRCUMSTANCE, WILL LIABILITY EXCEED THE ORIGINAL REGISTRATION FEE, REGARDLESS OF THE FORM OF CLAIM. Some states do not allow exclusion of the limit of liability for consequential or incidental damages, so the above limitation may not apply to you. This agreement shall be governed by the laws of the State of Colorado. Any action or proceeding brought by either party against the other arising out of or related to this agreement shall be brought only in a STATE or FEDERAL COURT of competent jurisdiction located in the State of Colorado. The parties hereby consent to in personam jurisdiction of said courts. ORDER FORM Name (required): __________________________________________________ Company (optional): _______________________________________________ Address: _________________________________________________________ City: _____________________________ State: ____ Zip: __________ Phone: ___________________ (Day) Where did you here about REBEL? ___________________________________ (AVALIABLE ONLY ON 3.5" DISKS) REBEL (spreadsheet) ($0.00) ____________ Your current version: _______________________ SCRIPT LIBRARY BUILDER: ($29.00) ____________ X _____________________________________________ (Print the name of INDIVIDUAL to be licensed) Includes printed manuals for REBEL and the Script Library Builder and source code for the script libraries. SCRIPT LIBRARY BUILDER (upgrade) ($14.00) ____________ Current License No. _________________________ Your current version: _______________________ SHIPPING AND HANDLING ($6.00 - $15.00 outside U.S.) 6.00 TOTAL (in US currency): ____________ REBEL SOFTWARE - P.O. Box 270277 - Fort Collins, CO - 80527